MySql 行转列的问题

昨天遇到了一个mysql数据库的查询问题，工作这么长时间很久没动数据库了，几乎忘记完了。真失败！

不过后来经过同事一指点，出来了。欣喜万分啊，这里写出来。

表1： cms_b_payment,其中 payment_method_id 是外键。

表2： cms_s_payment_method ，主键id是cms_b_payment的外键。

问题：  传入一个参数为分组类型：groupby varchar(20),参数可以为：year ,month ,week ,day

            根据传入的参数类型分组，比如说，按照天分组： 查出每天 付款方式为 Cash ,Medical Card, Health Care Voucher,VISA Card,Master Card 的各项总收入，其他的付款方式以 Others 代替。

结果为：

查询语句为：

select cr_dttm as create_date,sum(case when  name_en ='Cash' then amount else 0 end) as Cash,sum(case when name_en = 'Medical Card' then amount else 0 end) as 'Medical Card',sum(case when name_en = 'Health Care Voucher' then amount else 0 end) as 'Health Care Voucher',sum(case when name_en = 'VISA Card' then amount else 0 end) as 'VISA Card',sum(case when name_en = 'Master Card' then amount else 0 end) as 'Master Card',sum(case when name_en !='Cash' and name_en !='Medical Card' and name_en != 'Health Care Voucher'  and name_en != 'VISA Card' and name_en != 'Master Card' then amount else 0 end)as Othersfrom(select         case  "day"          when "day" then date_format(p.cr_dttm,"%Y-%m-%d")          when "week" then week(date_format(p.cr_dttm,'%Y-%m-%d'))          when "month" then month(date_format(p.cr_dttm,"%Y-%m-%d"))        end as cr_dttm,  pm.name_en name_en,sum(p.amount) amountfrom cms_b_payment p,cms_s_payment_method pmwhere pm.id= p.payment_method_idgroup by date_format(p.cr_dttm,"%Y-%m-%d"),p.payment_method_id)as table1group by create_date;

分析：

1.  

sum(case when  name_en ='Cash' then amount else 0 end) as Cash  是将得到的结果进行行转列求和的方式。

2.

case  "day"          when "day" then date_format(p.cr_dttm,"%Y-%m-%d")          when "week" then week(date_format(p.cr_dttm,'%Y-%m-%d'))          when "month" then month(date_format(p.cr_dttm,"%Y-%m-%d"))        end as cr_dttm,

case  "day"是传入的参数。 分别判断传入的参数，并格式化。

若参数为 day, 则 cr_dttm 显示的格式为2012-05-01

若参数为 week, 则 cr_dttm 显示的格式为 int 类型的第几周

若参数为 month, 则 cr_dttm 显示的格式为2012-05

若参数为 year, 则 cr_dttm 显示的格式为2012

顺便附上供大家参考学习：

MySql按周，按月，按日分组统计数据

select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;  select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;  select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

DATE_FORMAT(date,format)

根据format字符串格式化date值。下列修饰符可以被用在format字符串中：

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。