求SQL语句

kinsey 2006-11-27 05:32:56
我表中的信息如下:
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语句?谢谢了
...全文
169 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
懒得去死 2006-11-28
  • 打赏
  • 举报
回复
select completedate as myDate,
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
懒得去死 2006-11-28
  • 打赏
  • 举报
回复
用存储过程吧。
kinsey 2006-11-28
  • 打赏
  • 举报
回复
感觉这个应该可以通过JOIN实现,但是试了半天也没搞定:(

哪位高手帮帮我呀
kinsey 2006-11-28
  • 打赏
  • 举报
回复
mschen(Co-ok) ,谢谢您的回复。不过您给的统计出的结果是当天的,而不是我说的每天统计出前面所有的数据。
kinsey 2006-11-28
  • 打赏
  • 举报
回复
搞定了,用了一个left join,不过搞得有点复杂:)

select a.cd as comdate,
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

多谢各位指点:)
kinsey 2006-11-28
  • 打赏
  • 举报
回复
yueliangdao0608,您给的那个语句好像也不对,会重复统计多次。

可否指点一下这个存储过程或函数怎么写?谢谢了
mschen 2006-11-27
  • 打赏
  • 举报
回复
/* try like this */

select completedate as myDate,
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

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧