SQL求助^^^^^^^^^^^^^^^^^^^^^^^^^^^^
a 表
PORTAL_ID customs_name
----------- --------------------------------------------------
0 海关总署
91 广东分署
b表
GUID PORTAL_ID TYPE_ID MODULE_ID MSG_TYPE IS_GOOD
------------------------------------------------------- ----------- ----------- ----------- -----------
277d0e09-0fbb-4941-9229-7619a7ee2778 0 1 17754 0 1
55c92ca3-117f-48b1-b251-9435eb19598c 91 3342 9049 0 0
A7205118-EB52-4545-880E-1F2482E712CE 0 3889 1188 1 1
c3dba60d-d89c-482d-a74b-0259fe32f446 91 1461 10750 0 0
现在要的结果是如下的:
customs_name 服务总数 邮件服务 短信服务
海关总署 2 1 1
广东分署 2 2 0
那些邮件服务，短信服务，在是在b 表中用，MSG_TYPE区分的，0代表邮件，1代表短信
[解决办法]
select A.customs_name,
isnull(T.服务总数,0) as 服务总数,
isnull(T.邮件服务,0) as 邮件服务,
isnull(T.短信服务,0) as 短信服务
from A
left join
(select PORTAL_ID,
count(*) as 服务总数,
sum(case when MSG_TYPE=0 then 1 else 0 end) as 邮件服务 ,
sum(case when MSG_TYPE=1 then 1 else 0 end) as 短信服务
from b
group by PORTAL_ID) T
on A.PORTAL_ID=T.PORTAL_ID
[解决办法]
declare @a table(PORTAL_ID int, customs_name varchar(10))
insert into @a select 0, '海关总署 '
union all select 91, '广东分署 '
declare @b table(GUID varchar(50),PORTAL_ID int, TYPE_ID int, MODULE_DI int,MSG_TYPE int, IS_GOOD int)
insert into @b select '277d0e09-0fbb-4941-9229-7619a7ee2778 ',0,1,17754,0,1
union all select '55c92ca3-117f-48b1-b251-9435eb19598c ',91,3342,9049,0,0
union all select 'A7205118-EB52-4545-880E-1F2482E712CE ',0,3889,1188,1,1
union all select 'c3dba60d-d89c-482d-a74b-0259fe32f446 ',91,1461,10750,0,0
select customs_name=a.customs_name,
服务总数=count(MSG_TYPE),
邮件服务=sum(case when MSG_TYPE=0 then 1 else 0 end),
短信服务=sum(case when MSG_TYPE=1 then 1 else 0 end)
from @a a inner join @b b
on a.PORTAL_ID =b.PORTAL_ID
group by a.customs_name
order by a.customs_name desc
/*
(2 行受影响)
(4 行受影响)
customs_name 服务总数 邮件服务 短信服务
------------ ----------- ----------- -----------
海关总署 2 1 1
广东分署 2 2 0
(2 行受影响)
*/
[解决办法]
---抱歉更正下
----借带头大哥的数据
declare @a table(PORTAL_ID int, customs_name varchar(10))
insert into @a select 0, '海关总署 '
union all select 91, '广东分署 '
declare @b table(GUID varchar(50),PORTAL_ID int, TYPE_ID int, MODULE_DI int,MSG_TYPE int, IS_GOOD int)
insert into @b select '277d0e09-0fbb-4941-9229-7619a7ee2778 ',0,1,17754,0,1
union all select '55c92ca3-117f-48b1-b251-9435eb19598c ',91,3342,9049,0,0
union all select 'A7205118-EB52-4545-880E-1F2482E712CE ',0,3889,1188,1,1
union all select 'c3dba60d-d89c-482d-a74b-0259fe32f446 ',91,1461,10750,0,0
---查询结果
Select
A.customs_name,
B.服务总数,
B.邮件服务,
B.短信服务
From
@a A
Inner Join (
Select PORTAL_ID,
Sum(Case MSG_TYPE When 0 then 1 Else 0 End) As 邮件服务,
Sum(Case MSG_TYPE When 1 then 1 Else 0 End) As 短信服务,
Count(*) As 服务总数
From @b Group By PORTAL_ID
) B
ON A.PORTAL_ID=B.PORTAL_ID
/*
customs_name 服务总数 邮件服务 短信服务
------------ ----------- ----------- -----------
海关总署 2 1 1
广东分署 2 2 0
（所影响的行数为 2 行）
*/
