我是数据库方面的小菜鸟,现在有一个表主要子段为id,date,type。具体数据举例:
id date type
1 2007-03-01 3:11:00 001
2 2007-03-01 6:20:08 001
3 2007-03-02 3:11:00 002
4 2007-03-02 6:20:08 001
5 2007-03-05 3:11:00 005
6 2007-03-05 6:20:08 001
现在想统计一下每一天各种类型数量,sql该怎么写?
结果如下
1 2 5
001 2 1 1
002 0 1 0
005 0 0 1
id date type
1 2007-03-01 3:11:00 001
2 2007-03-01 6:20:08 001
3 2007-03-02 3:11:00 002
4 2007-03-02 6:20:08 001
5 2007-03-05 3:11:00 005
6 2007-03-05 6:20:08 001
现在想统计一下每一天各种类型数量,sql该怎么写?
结果如下
1 2 5
001 2 1 1
002 0 1 0
005 0 0 1
sum(if(day(date)=1,1,0)),
sum(if(day(date)=2,1,0)),
sum(if(day(date)=4,1,0))
from 现在有一个表
group by type
sum(if(day(date)=1,1,0)),
sum(if(day(date)=2,1,0)),
sum(if(day(date)=4,1,0))
from tt group by type
如果日期多, 要用SP生成SQL语句,全月的要生成每天的日期