mysql多行的一列值转一行的一列值 以及ifelse

 

 

SELECT ha.name  ,bp.id ,'月度结算' AS payoff_type , bp.payoff_year_month , 
(
        CASE bp.payoff_status
            WHEN 1 THEN '结算表制作中'
            WHEN 2 THEN '结算表制作完成'          
            WHEN 8 THEN '已完成'
            ELSE NULL
        END
    ) payoff_status,
 su.real_name  ,tep.name_fz   FROM bu_payoff bp 
LEFT JOIN hr_agency ha ON ha.id = bp.agency_id
LEFT JOIN sys_user su ON su.user_id = bp.create_user
LEFT JOIN (
SELECT bua.agency_id, GROUP_CONCAT(su.real_name  Separator ',') as name_fz FROM bu_user_agency bua LEFT JOIN sys_user su ON su.user_id = bua.user_id  GROUP BY bua.agency_id
) tep ON  ha.id = tep.agency_id