有个“出勤表/cq” ,字段有“员工ID/yg_id”,“出勤/cqgt”,“日期/rq” 统计每个人每天的出勤时间 姓名 1号 2号 3号 4号 5号 6号 7号。王名 1.1 1.3 0.8 0.7李明 0.9 1.0 0.6 1.8按照这种格式输出,这个sql语句该怎么写啊(是mysql数据库)
调试欢乐多
sum(if(DATE_FORMAT(rq,'%d')='1',cqgt,0)) as 1号,
sum(if(DATE_FORMAT(rq,'%d')='2',cqgt,0)) as 2号,
....
FROM cq GROUP BY yg_id
select SEC_TO_TIME(sum(TIME_TO_SEC(rq))) col,yg_id from cq group by DATE_FORMAT(rq, '%Y-%m-%d'))
sum(if(day(rq)=1 ,cqgt,0)) as `1 号`,
sum(if(day(rq)=2 ,cqgt,0)) as `2 号`,
sum(if(day(rq)=3 ,cqgt,0)) as `3 号`,
sum(if(day(rq)=4 ,cqgt,0)) as `4 号`,
sum(if(day(rq)=5 ,cqgt,0)) as `5 号`,
sum(if(day(rq)=6 ,cqgt,0)) as `6 号`,
sum(if(day(rq)=7 ,cqgt,0)) as `7 号`,
sum(if(day(rq)=8 ,cqgt,0)) as `8 号`,
sum(if(day(rq)=9 ,cqgt,0)) as `9 号`,
sum(if(day(rq)=10,cqgt,0)) as `10号`,
sum(if(day(rq)=11,cqgt,0)) as `11号`,
sum(if(day(rq)=12,cqgt,0)) as `12号`,
sum(if(day(rq)=13,cqgt,0)) as `13号`,
sum(if(day(rq)=14,cqgt,0)) as `14号`,
sum(if(day(rq)=15,cqgt,0)) as `15号`,
sum(if(day(rq)=16,cqgt,0)) as `16号`,
sum(if(day(rq)=17,cqgt,0)) as `17号`,
sum(if(day(rq)=18,cqgt,0)) as `18号`,
sum(if(day(rq)=19,cqgt,0)) as `19号`,
sum(if(day(rq)=20,cqgt,0)) as `20号`,
sum(if(day(rq)=21,cqgt,0)) as `21号`,
sum(if(day(rq)=22,cqgt,0)) as `22号`,
sum(if(day(rq)=23,cqgt,0)) as `23号`,
sum(if(day(rq)=24,cqgt,0)) as `24号`,
sum(if(day(rq)=25,cqgt,0)) as `25号`,
sum(if(day(rq)=26,cqgt,0)) as `26号`,
sum(if(day(rq)=27,cqgt,0)) as `27号`,
sum(if(day(rq)=28,cqgt,0)) as `28号`,
sum(if(day(rq)=29,cqgt,0)) as `29号`,
sum(if(day(rq)=30,cqgt,0)) as `30号`,
sum(if(day(rq)=31,cqgt,0)) as `31号`
from cq
group by yg_id