SQL2005的环境下
1.表名:Message(有关消息的表)
2.字段
id int 主键 identity
type Char(1) 0-普通消息; 1-流程消息; 2-普通建议; 3-工程建议
isRead Char(1) 0-未读;1-已读
3.我想得到的是: 未读消息总数 普通消息数量 流程消息数量 普通建议数量 工程建议数量
100 25 25 25 25
4.我的语句分开的都会写:
select count(*) 未读消息总数 from message where isread = 0select count(*) 普通消息数量 from message where type = 0 and isread = 0select count(*) 流程消息数量 from message where type = 1 and isread = 0select count(*) 普通建议数量 from message where type = 2 and isread = 0select count(*) 工程建议数量 from message where type = 3 and isread = 0
5.但是合在一起怎么写呢?用Case.When嘛?请多指教!
1.表名:Message(有关消息的表)
2.字段
id int 主键 identity
type Char(1) 0-普通消息; 1-流程消息; 2-普通建议; 3-工程建议
isRead Char(1) 0-未读;1-已读
3.我想得到的是: 未读消息总数 普通消息数量 流程消息数量 普通建议数量 工程建议数量
100 25 25 25 25
4.我的语句分开的都会写:
select count(*) 未读消息总数 from message where isread = 0select count(*) 普通消息数量 from message where type = 0 and isread = 0select count(*) 流程消息数量 from message where type = 1 and isread = 0select count(*) 普通建议数量 from message where type = 2 and isread = 0select count(*) 工程建议数量 from message where type = 3 and isread = 0
5.但是合在一起怎么写呢?用Case.When嘛?请多指教!
未读消息总数=count(*),
普通消息数量=sum(case type when 0 then 1 else 0 end),
流程消息数量=sum(case type when 1 then 1 else 0 end),
普通建议数量=sum(case type when 2 then 1 else 0 end),
工程建议数量=sum(case type when 3 then 1 else 0 end)
from
message
where
isread = 0
(select count(*) from message where isread = 0) as 未读消息总数,
(select count(*) from message where type = 0 and isread = 0) as 普通消息数量,
(select count(*) from message where type = 1 and isread = 0) as 流程消息数量,
(select count(*) from message where type = 2 and isread = 0) as 普通建议数量,
(select count(*) from message where type = 3 and isread = 0) as 工程建议数量select
sum(case when isread = 0 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 0 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 1 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 2 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 3 then 1 end) as 未读消息总数
from message
sum(case when isread = 0 then 1 else 0 end) as 未读消息总数,
sum(case when isread = 0 and type = 0 then 1 else 0 end) as 普通消息数量,
sum(case when type = 1 and isread = 0 then 1 else 0 end) as 流程消息数量,
sum(case when type = 2 and isread = 0 then 1 else 0 end) as 普通建议数量,
sum(case when type = 3 and isread = 0 then 1 else 0 end) as 工程建议数量
from
message
sum(case when [type] = 1 and isread = 0 then 1 else 0 end)普通消息数量,
sum(case when [type] = 1 and isread = 0 then 1 else 0 end)流程消息数量,
sum(case when [type] = 2 and isread = 0 then 1 else 0 end)普通建议数量,
sum(case when [type] = 3 and isread = 0 then 1 else 0 end)工程建议数量
from message
SUM (CASE WHEN isread =0 AND TYPE>=0 THEN 1 ELSE 0 END)AS 未读消息总数,
SUM (CASE WHEN type = 0 and isread = 0 THEN 1 ELSE 0 END)AS 普通消息数量,
SUM (CASE WHEN type = 1 and isread = 0 THEN 1 ELSE 0 END)AS 流程消息数量,
SUM (CASE WHEN type = 2 and isread = 0 THEN 1 ELSE 0 END)AS 普通建议数量,
SUM (CASE WHEN type = 3 and isread = 0 THEN 1 ELSE 0 END)AS 工程建议数量
FROM TB
select
sum(case when isread = 0 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 0 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 1 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 2 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 3 then 1 end) as 未读消息总数
from
message
SUM (CASE WHEN isread =0 THEN 1 ELSE 0 END)AS 未读消息总数,
SUM (CASE WHEN type = 0 and isread = 0 THEN 1 ELSE 0 END)AS 普通消息数量,
SUM (CASE WHEN type = 1 and isread = 0 THEN 1 ELSE 0 END)AS 流程消息数量,
SUM (CASE WHEN type = 2 and isread = 0 THEN 1 ELSE 0 END)AS 普通建议数量,
SUM (CASE WHEN type = 3 and isread = 0 THEN 1 ELSE 0 END)AS 工程建议数量
FROM TB
未读消息总数=count(*),
普通消息数量=sum(case type when 0 then 1 else 0 end),
流程消息数量=sum(case type when 1 then 1 else 0 end),
普通建议数量=sum(case type when 2 then 1 else 0 end),
工程建议数量=sum(case type when 3 then 1 else 0 end)
from
message
sum(case isRead when '0' then 1 else 0 end) as 未读消息总数
,sum(case type when '0' then 1 else 0 end) as 普通消息数量
,sum(case type when '1' then 1 else 0 end) as 流程消息数量
,sum(case type when '2' then 1 else 0 end) as 普通建议数量
,sum(case type when '3' then 1 else 0 end) as 工程建议数量
from Message
select
sum(case when isread = 0 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 0 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 1 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 2 then 1 end) as 未读消息总数,
sum(case when isread = 0 and isread = 3 then 1 end) as 未读消息总数
from
message