关于#mysql#的问题：mysql 如何查询实现excel 的这种效果，横向显示考试月份 examMonth，纵向显示列表数据

Question

mysql 如何查询，最后导出实现excel 的这种效果，横向显示考试月份 examMonth，纵向显示列表数据

 SELECT * from (
         SELECT bkhcode,bkhName,examMonth,score,performanceLevel,salaryPayment from t_kh_month_old o1 
         where  status=9 
         union
         SELECT bkhcode,bkhName,examMonth,score,performanceLevel,salaryPayment from t_kh_month_new n1 
         where  status=9 
     ) t where 1=1  ORDER BY examMonth 

Answer 1

使用行列转换，具体要看你的查询结果来写sql。
就像这样，望采纳，谢谢！

SELECT * from (
         SELECT bkhcode,bkhName,sum(case when examMonth = '1月份' then score else 0 end) as "1月份",sum(case when examMonth = '2月份' then score else 0 end) as "2月份",sum(case when examMonth = '3月份' then score else 0 end) as "3月份",performanceLevel,salaryPayment from t_kh_month_old o1 
         where  status=9 group by bkhcode,bkhName,performanceLevel,salaryPayment
         union
         SELECT bkhcode,bkhName,sum(case when examMonth = '1月份' then score else 0 end) as "1月份",sum(case when examMonth = '2月份' then score else 0 end) as "2月份",sum(case when examMonth = '3月份' then score else 0 end) as "3月份",performanceLevel,salaryPayment from t_kh_month_new n1 
         where  status=9 group by bkhcode,bkhName,performanceLevel,salaryPayment
     ) t