我表中的信息如下:
id-工作包id,name-工人名,CompleteDate-完成日期
id Name CompleteDate
1 Worker1 2006-11-24
2 Worker2 2006-11-24
3 Worker3 2006-11-24
4 Worker2 2006-11-25
5 Worker2 2006-11-25
6 Worker3 2006-11-25
7 Worker1 2006-11-26现在我想统计每天每个工人的进度,得到一个趋势表。
myDate Worker1 Worker2 Worker3 Sum
2006-11-24 1 1 1 3
2006-11-25 1 3 2 6
2006-11-26 2 3 2 7哪位高手给个SQL语句?谢谢了
id-工作包id,name-工人名,CompleteDate-完成日期
id Name CompleteDate
1 Worker1 2006-11-24
2 Worker2 2006-11-24
3 Worker3 2006-11-24
4 Worker2 2006-11-25
5 Worker2 2006-11-25
6 Worker3 2006-11-25
7 Worker1 2006-11-26现在我想统计每天每个工人的进度,得到一个趋势表。
myDate Worker1 Worker2 Worker3 Sum
2006-11-24 1 1 1 3
2006-11-25 1 3 2 6
2006-11-26 2 3 2 7哪位高手给个SQL语句?谢谢了
解决方案 »
- 求问oracle向mysql迁移的问题
- mysql 误删表中数据 能恢复原数据吗
- 关于MySQL在Linux中的问题
- 启动的时候显示Timeout error occurred trying to start MySQL Daemon.
- 有关MySQL5.1版本在linux 4下的安装问题,急!!!
- 有谁可以帮我解析一下这里的SQL语句啊?
- 想学习j2ee用mysql做数据库,求点建议
- 奇怪的語句
- JSP+MYSQL 显示数据排序的问题 不管以where字段来选择显示,都能数据表按123456。求教
- 关于protege连接mysql的问题
- 怎样取出日期 中的月份
- #1146 - Table 'servicedesk.aaaaccadminprofile' doesn't exist 这是什么原因?
sum(if(name='worker1',1,0)) as worker1,
sum(if(name='worker2',1,0)) as worker2,
sum(if(name='worker3',1,0)) as worker3,
sum(1) as sum
from test
group by completedate
sum(if(name='worker1',1,0)) as worker1,
sum(if(name='worker2',1,0)) as worker2,
sum(if(name='worker3',1,0)) as worker3,
sum(1) as sum
from test a, test b,
where a.completedate != b.completedate group by completedate
sum(b.w1) as worker1,
sum(b.w2) as worker2,
sum(b.w3) as worker3,
sum(b.s) as sum
from (select completedate as cd,
sum(if(name='worker1',1,0)) as w1,
sum(if(name='worker2',1,0)) as w2,
sum(if(name='worker3',1,0)) as w3,
sum(1) as s) as a left join
(select completedate as cd,
sum(if(name='worker1',1,0)) as w1,
sum(if(name='worker2',1,0)) as w2,
sum(if(name='worker3',1,0)) as w3,
sum(1) as s) as b on b.cd<=a.cd
group by comdate多谢各位指点:)