有一张表t_sendmessage我想统计一下数据,表结构如下
send_id send_inv send_message send_time send_sign
1 89 新年快乐! 2010-02-21 5:51:20 0
2 90 新年快乐! 2010-02-21 6:45:21 1
3 91 你好! 2010-02-22 10:50:21 0
4 92 呵呵 2010-02-22 12:24:23 1
4 93 呵呵 2010-02-22 12:15:08 0
我想按天来统计查询 查询结果应为
send_time count(id) sign1 sign2
2010-02-21 2 1 1
2010-02-22 3 2 1
谁能帮我下,谢谢了!
send_id send_inv send_message send_time send_sign
1 89 新年快乐! 2010-02-21 5:51:20 0
2 90 新年快乐! 2010-02-21 6:45:21 1
3 91 你好! 2010-02-22 10:50:21 0
4 92 呵呵 2010-02-22 12:24:23 1
4 93 呵呵 2010-02-22 12:15:08 0
我想按天来统计查询 查询结果应为
send_time count(id) sign1 sign2
2010-02-21 2 1 1
2010-02-22 3 2 1
谁能帮我下,谢谢了!
count(send_id),
sum(case send_sign when 0 then 1 end) as sign1,
sum(case send_sign when 1 then 1 end) as sign2
from t_sendmessage
group by convert(varchar(10),send_time,102)
convert(varchar(10),send_time,120) as send_time,
count(send_id) as [count(id)],
sum(case when send_sign=0 then 1 end) as sign1,
sum(case when send_sign=1 then 1 end) as sign2
from t_sendmessage
group by convert(varchar(10),send_time,120)