表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 代替。
结果为:
查询语句为:
[sql] view plaincopy
selectcr_dttmascreate_date,
sum(casewhenname_en ='Cash'thenamountelse0end)asCash,
sum(casewhenname_en ='Medical Card'thenamountelse0end)as'Medical Card',
sum(casewhenname_en ='Health Care Voucher'thenamountelse0end)as'Health Care Voucher',
sum(casewhenname_en ='VISA Card'thenamountelse0end)as'VISA Card',
sum(casewhenname_en ='Master Card'thenamountelse0end)as'Master Card',
sum(casewhenname_en !='Cash'andname_en !='Medical Card'andname_en !='Health Care Voucher'andname_en !='VISA Card'andname_en !='Master Card'thenamountelse0end)asOthers
from
(select
case"day"
when"day"thendate_format(p.cr_dttm,"%Y-%m-%d")
when"week"thenweek(date_format(p.cr_dttm,'%Y-%m-%d'))
when"month"thenmonth(date_format(p.cr_dttm,"%Y-%m-%d"))
endascr_dttm,
pm.name_en name_en,sum(p.amount) amount
fromcms_b_payment p,cms_s_payment_method pm
wherepm.id= p.payment_method_id
groupbydate_format(p.cr_dttm,"%Y-%m-%d"),p.payment_method_id)astable1
groupbycreate_date;
分析:
1.
[sql] view plaincopy
sum(casewhenname_en ='Cash'thenamountelse0end)asCash 是将得到的结果进行行转列求和的方式。
2.
[sql] view plaincopy
case"day"
when"day"thendate_format(p.cr_dttm,"%Y-%m-%d")
when"week"thenweek(date_format(p.cr_dttm,'%Y-%m-%d'))
when"month"thenmonth(date_format(p.cr_dttm,"%Y-%m-%d"))
endascr_dttm,
case "day"是传入的参数。 分别判断传入的参数,并格式化。
若参数为 day, 则 cr_dttm 显示的格式为2012-05-01
若参数为 week, 则 cr_dttm 显示的格式为 int 类型的第几周
若参数为 month, 则 cr_dttm 显示的格式为2012-05
若参数为 year, 则 cr_dttm 显示的格式为2012
顺便附上供大家参考学习:
MySql按周,按月,按日分组统计数据
[sql] view plaincopy
selectDATE_FORMAT(create_time,'%Y%u') weeks,count(caseid)countfromtc_casegroupbyweeks;
selectDATE_FORMAT(create_time,'%Y%m%d') days,count(caseid)countfromtc_casegroupbydays;
selectDATE_FORMAT(create_time,'%Y%m') months,count(caseid)countfromtc_casegroupbymonths;
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), 这里星期一是星期的第一天%% 一个文字“%”。