当前位置：首页 > news >正文

MySql 按姓氏的笔画顺序排序

news 2026/3/26 17:35:12

需求：副部门中姓名按姓氏笔画排序，第一个姓相同的排第二个字，依此类推，并用‘，’隔开；部门（tjfh）用拼音排序

解决方式：部门（tjfh）用CONVERT(tjfh USING GBK)；副部门中姓名排序需要首先维护一个chinese_stroke_order 表，里面有每个中文字的笔画顺序。

笔画顺序排序.xlsx链接: https://pan.baidu.com/s/1Pgtc8n22Ah4U5sHr_PJJ8w?pwd=9uas

SELECTtjfh,F_Id,MAX(zwwy) AS zwwy,GROUP_CONCAT(fzwwy ORDER BY oc1, oc2, oc3 ASC SEPARATOR '、') AS A_xm_concat,MAX(msz) AS msz,MAX(slsj) AS slsj
FROM (SELECTa.A_mc AS tjfh,a.F_Id,DATE_FORMAT(a.A_slsj, '%Y-%m-%d') AS slsj,CASE WHEN zw.comInputField103='1' THEN xh.A_xm ELSE '' END AS zwwy,CASE WHEN zw.comInputField103='2' THEN xh.A_xm ELSE NULL END AS fzwwy,CASE WHEN zw.comInputField103='3' THEN xh.A_xm ELSE '' END AS msz,cso1.order_code AS oc1,cso2.order_code AS oc2,cso3.order_code AS oc3FROM fhjggl1 aLEFT JOIN xhzh_hzdjb_zbshttdrzwj1l AS z ON a.F_Id  =z.tjfhLEFT JOIN xhzh_hzd1jb xh ON xh.F_Id = z.MAIN_IDLEFT JOIN auto_tab1le_0245 zw ON zw.id =z.A_zwLEFT JOIN chinese_stroke_order AS cso1 ON cso1.name =left(xh.A_xm, 1)LEFT JOIN chinese_stroke_order AS cso2 ON cso2.name =SUBSTRING(xh.A_xm, 2, 1)LEFT JOIN chinese_stroke_order AS cso3 ON cso3.name =SUBSTRING(xh.A_xm, 3, 1) WHERE length(a.A_jgbm)=5 and  (a.F_DeleteMark=0 or a.F_DeleteMark is null)  and (xh.F_DeleteMark=0 or xh.F_DeleteMark is null)and COALESCE(xh.A_xm,'')<>''
) a  
GROUP BY tjfh,F_Id 
ORDER BY  CONVERT(tjfh USING GBK)