查询整体和部分（不使用存储过程）

Question

现由两张表，infomember和societyinfo。其中infomember的表结构如下societyinfo的表结构如下：其中，想要根据societyinfo中的contentType进行分类，现在contentType有来信，有效，平安三类，查询出
infomember中的township分别对应的三类信息数量和数量之和，如下图所示：

Answer 1

USE test
GO---->生成表infomember
--
--if object_id('infomember') is not null 
-- drop table infomember
--Go
--Create table infomember([id] smallint,[contentType] nvarchar(2),[township] nvarchar(3))
--Insert into infomember
--Select 1,N'来信',N'张村镇'
--Union all Select 2,N'有效',N'张村镇'
--Union all Select 3,N'平安',N'张村镇'
--Union all Select 4,N'来信',N'羊亭镇'
--Union all Select 5,N'有效',N'羊亭镇'
--Union all Select 6,N'平安',N'羊亭镇'
--
--
---->生成表societyinfo
--
--if object_id('societyinfo') is not null 
-- drop table societyinfo
--Go
--Create table societyinfo([id] smallint,[contentType] nvarchar(2),[infoMember_id] smallint)
--Insert into societyinfo
--Select 1,N'来信',1
--Union all Select 2,N'来信',1
--Union all Select 3,N'来信',1
--Union all Select 4,N'来信',1
--Union all Select 5,N'来信',1
--Union all Select 6,N'有效',2
--Union all Select 7,N'有效',2
--Union all Select 8,N'有效',2
--Union all Select 9,N'平安',3
--Union all Select 10,N'平安',3
--Union all Select 11,N'平安',3
--Union all Select 12,N'平安',3
--Union all Select 13,N'来信',4
--Union all Select 14,N'来信',4
--Union all Select 15,N'来信',4
--Union all Select 16,N'有效',5
--Union all Select 17,N'有效',5
--Union all Select 18,N'有效',5
--Union all Select 19,N'有效',5
--Union all Select 20,N'有效',5
--Union all Select 21,N'平安',6
--Union all Select 22,N'平安',6
Select
ROW_NUMBER()OVER(ORDER BY MIN(a.id)) AS 序号
,a.township
,SUM(b.info_Qty) AS 报送信息数
,MAX(CASE WHEN b.contentType=N'有效' THEN b.info_Qty END) AS 有效信息数
,MAX(CASE WHEN b.contentType=N'来信' THEN b.info_Qty END) AS 来信数
-- ,MAX(CASE WHEN b.contentType=N'平安' THEN b.info_Qty END) AS 平安信息数
from infomember As a
CROSS APPLY (Select 
infoMember_id 
,contentType
,Count(id) As info_Qty
from societyinfo AS x
WHERE x.infoMember_id=a.id
Group by 
infoMember_id
,contentType
) As b 
GROUP BY 
a.township
/*
序号   township  报送信息数  有效信息数  来信数
------ -------- ----------- ----------- -----------
1      张村镇      12          3           5
2      羊亭镇      10          5           3
*/

Answer 2

DECLARE @infomember table
(
id int NOT NULL PRIMARY KEY, 
township nvarchar(20)
)DECLARE @societyinfo table
(
infomemberid int,
contenttype nvarchar(10),
number int
)INSERT INTO @infomember
SELECT 1, '张村镇' UNION ALL
SELECT 2, '杨婷镇' INSERT INTO @societyinfo 
SELECT 1, '来信', 1 UNION ALL
SELECT 1, '来信', 2 UNION ALL
SELECT 1, '有效', 3 UNION ALL
SELECT 1, '有效', 1 UNION ALL
SELECT 1, '平安', 1 UNION ALL
SELECT 1, '平安', 1 UNION ALL
SELECT 2, '来信', 1 UNION ALL
SELECT 2, '来信', 2 UNION ALL
SELECT 2, '有效', 3 UNION ALL
SELECT 2, '有效', 1 UNION ALL
SELECT 2, '平安', 1 UNION ALL
SELECT 2, '平安', 1 SELECT township, a.来信,a.平安,a.有效
FROM @infomember b
INNER JOIN
(SELECT 
infomemberid,
SUM(CASE WHEN contenttype = '来信' THEN number ELSE 0 END) AS 来信,
SUM(CASE WHEN contenttype = '平安' THEN number ELSE 0 END) AS 平安,
SUM(CASE WHEN contenttype = '有效' THEN number ELSE 0 END) AS 有效
FROM @societyinfo
GROUP BY infomemberid)a
ON b.id = a.infomemberid有感而发  俺也写一个