查询不会写。。

[joejoe1991]

表结构：

CREATE TABLE [msgToShi] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[hAdmin] [int] NOT NULL ,
	[hTg] [bit] NOT NULL CONSTRAINT [DF_msgToShi_hTg] DEFAULT (0),
	[hBslb] [int] NOT NULL CONSTRAINT [DF_msgToShi_hBslb] DEFAULT (0),
	[hYd] [bit] NOT NULL CONSTRAINT [DF_msgToShi_hYd] DEFAULT (0),
	CONSTRAINT [PK_msgToShi] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

表里现有的数据：

ID          hAdmin      hTg  hBslb       hYd  
----------- ----------- ---- ----------- ---- 
1           2           0    1           0
2           2           0    0           0
3           2           1    1           0
3           2           1    2           0
4           3           0    0           0

hAdmin 表示消息的接收者

hTg(是否通过) 为0 的时候，表示 “未通过”，为1的时候表示“通过”

hYd(是否已读) 为0的时候，表示“未读”，1表示“已读”

hBslb(报送类别) 0表示“新增”，1表示“变更”，2表示“终止”

现在我要这样的结果：

报送类别 通过 未通过
新增	  0	1
变更	  1	1
终止	  1	0

统计hAdmin为2的所有的未读消息。
这个sql 应该怎么写呢？
多谢！！！！！！

[liangCK]

select case bhslb when 0 then '新增' 
                  when 1 then '变更'
                  when 2 then '终止'
          end as 报送类别,
       count(case htg when 1 then 1 end) [通过],
       count(case htg when 0 then 1 end) [未通过]
from tb
where hadmin=2
group by bhslb

[子陌红尘]

select
    报送类别,
    sum(hTg  ) as 通过,
    sum(1-hTg) as 未通过
from
    (select case hBslb when 0 then '新增' when 1 then '变更' when 2 then '终止' end as 报送类别,* from msgToShi) t
where
    hAdmin=2 and hYd=0
group by
    报送类别

[phxiao8512]

2楼:

服务器: 消息 409，级别 16，状态 2，行 1
sum or average aggregate 运算不能以 bit 数据类型作为参数?

[liangCK]

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,hAdmin int,hTg int,hBslb int,hYd int)
insert into #T
select 1,2,0,1,0 union all
select 2,2,0,0,0 union all
select 3,2,1,1,0 union all
select 3,2,1,2,0 union all
select 4,3,0,0,0

select case hBslb when 0 then '新增' 
                  when 1 then '变更'
                  when 2 then '终止'
          end as 报送类别,
       count(case htg when 1 then 1 end) [通过],
       count(case htg when 0 then 1 end) [未通过]
from #T
where hadmin=2
group by hBslb


/*
报送类别 通过          未通过
---- ----------- -----------
新增   0           1
变更   1           1
终止   1           0
*/

[tianyusunkuangyu]

sum or average aggregate 运算不能以 bit 数据类型作为参数
因为bit 数据只有1，0 所以不能求和呀

[nzperfect]

select 
case hBslb when 0 then '新增' 
                  when 1 then '变更'
                  when 2 then '终止'
          end as 报送类别,
       isnull(sum(case htg when 1 then 1 end),0) [通过],
       isnull(sum(case htg when 0 then 1 end),0) [未通过]
 
from [msgToShi] as a where hadmin='2' and hYd='0'
group by hBslb order by hBslb

[子陌红尘]

select
    报送类别,
    sum(case hTg when 1 then 1 else 0 end) as 通过,
    sum(case hTg when 1 then 0 else 1 end) as 未通过
from
    (select case hBslb when 0 then '新增' when 1 then '变更' when 2 then '终止' end as 报送类别,* from msgToShi) t
where
    hAdmin=2 and hYd=0
group by
    报送类别

[ojuju10]

if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,hAdmin int,hTg int,hBslb int,hYd int)
insert into #T
select 1,2,0,1,0 union all
select 2,2,0,0,0 union all
select 3,2,1,1,0 union all
select 3,2,1,2,0 union all
select 4,3,0,0,0

select case when hbslb=0 then '新增'
when hbslb=1 then '更改'
else '终止' end as 报送类型
,max(case when htg=1 then 1 else 0 end) as '通过',
max(case when htg=0 then 1 else 0 end) as '未通过'
 from #t
group by hbslb

报送类型 通过          未通过
---- ----------- -----------
新增   0           1
更改   1           1
终止   1           0

(3 行受影响)

[让你望见影子的墙]

select case hBslb when 0 then '新增'
when 1 then '变更'
when 2 then '终止'
end as 报送类别,
count(case htg when 1 then 1 end) [通过],
count(case htg when 0 then 1 end) [未通过]
from #T
where hadmin=2 and hyd=0
group by hBslb

[中国风]

if not object_id('msgToShi') is null
	drop table msgToShi
Go
Create table msgToShi([ID] int,[hAdmin] int,[hTg] int,[hBslb] int,[hYd] int)
Insert msgToShi
select 1,2,0,1,0 union all
select 2,2,0,0,0 union all
select 3,2,1,1,0 union all
select 3,2,1,2,0 union all
select 4,3,0,0,0
Go
select 
	case hBslb when 0 then '新增' 
              when 1 then '变更'
              when 2 then '终止'
          end as 报送类别,
	[0] as '未通过',
	[1] as '通过'
from 
	(Select [ID],[hBslb], [hTg] from  msgToShi  where hAdmin=2 )a
pivot 
	(count([ID]) for [hTg] in([0],[1]))b