MySql 按姓氏的笔画顺序排序

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

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

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

SELECT
    tjfh,
    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 (
    SELECT
        a.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 oc3
    FROM fhjggl1 a
    LEFT JOIN xhzh_hzdjb_zbshttdrzwj1l AS z ON a.F_Id  =z.tjfh
    LEFT JOIN xhzh_hzd1jb xh ON xh.F_Id = z.MAIN_ID
    LEFT JOIN auto_tab1le_0245 zw ON zw.id =z.A_zw
    LEFT 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)