表中有2个标识字段FIsRead,FIsNotify都是 bit型的FIsRead FIsFeedback
------- -----------
1 1
0 0
1 1
1 0
0 1
1 0
现在要一起统计FIsRead 为1和FIsFeedback为1的行数
在一个结果集中
FIsRead FIsFeedback
------- -----------
4 3这个语句怎么写啊?
------- -----------
1 1
0 0
1 1
1 0
0 1
1 0
现在要一起统计FIsRead 为1和FIsFeedback为1的行数
在一个结果集中
FIsRead FIsFeedback
------- -----------
4 3这个语句怎么写啊?
sum(case FIsRead when 1 then 1 else 0 end) as FIsRead,
sum(case FIsFeedback when 1 then 1 else 0 end) as FIsFeedback
from table
insert @t values(1,1)
insert @t values(0,0)
insert @t values(1,1)
insert @t values(1,0)
insert @t values(0,1)
insert @t values(1,0)select
sum(case FIsRead when 1 then 1 else 0 end) as FIsRead,
sum(case FIsFeedback when 1 then 1 else 0 end) as FIsFeedback
from @t/*结果
FIsRead FIsFeedback
----------- -----------
4 3
*/
insert @t values(1,1)
insert @t values(0,0)
insert @t values(1,1)
insert @t values(1,0)
insert @t values(0,1)
insert @t values(1,0)select
sum(case FIsRead when 1 then 1 else 0 end) as FIsRead1,
sum(case FIsFeedback when 1 then 1 else 0 end) as FIsFeedback1,
sum(case FIsRead when 0 then 1 else 0 end) as FIsRead,
sum(case FIsFeedback when 0 then 1 else 0 end) as FIsFeedback0
from @t/*结果
FIsRead1 FIsFeedback1 FIsRead FIsFeedback0
----------- ------------ ----------- ------------
4 3 2 3
*/
如果我想要top 1即第一条的内容怎么办
/*结果
FIsRead FIsFeedback content
----------- ----------- ----------------------------
4 3 你好,这是你的短信内容
*/
这个怎么写?
declare @t table(FIsRead bit, FIsFeedback bit)
insert @t values(1,1)
insert @t values(0,0)
insert @t values(1,1)
insert @t values(1,0)
insert @t values(0,1)
insert @t values(1,0)select
sum(case FIsRead when 1 then 1 else 0 end) as FIsRead,
sum(case FIsFeedback when 1 then 1 else 0 end) as FIsFeedback
from @t/*结果
FIsRead FIsFeedback
----------- -----------
4 3
*/
-----------
不是得到了這個結果嗎?
-----------------------------------------------------------
是要FIsRead =1 的TOP 1 还是FIsRead =0 的TOP 1 ?
是要FIsFeedback = 1 的TOP 1 还是FIsFeedback = 0 的TOP 1 ?
insert @t values(1,1,'你好,这是你的短信内容')
insert @t values(0,0,'你好')
insert @t values(1,1,'你好,这是')
insert @t values(1,0,'你好,这是你')
insert @t values(0,1,'你好,这是你的')
insert @t values(1,0,'你好,这是你的短信')select
sum(case FIsRead when 1 then 1 else 0 end) as FIsRead1,
sum(case FIsFeedback when 1 then 1 else 0 end) as FIsFeedback1,
sum(case FIsRead when 0 then 1 else 0 end) as FIsRead0,
sum(case FIsFeedback when 0 then 1 else 0 end) as FIsFeedback0,
(select top 1 content from @t where FIsRead = 1) as content
from @t/*结果
FIsRead1 FIsFeedback1 FIsRead0 FIsFeedback0 content
----------- ------------ ----------- ------------ -----------------------
4 3 2 3 你好,这是你的短信内容
*/