库为bt_201110
人口表 resident里有民族表 nation和性别表 sex，
表nation结构为：
NATION_CODE NATION_NAME
1 汉族
10 朝鲜族
11 满族
表sex结构为：
Code CodeName
1 男
2 女
0 未知的性别
9 未说明的性别
请问做这样的报表统计如何用sql来实现呢？求高手指点～
之前的贴不知道往哪里回复 只好重新发详细的帖
序号 民族 合计 男 女
合计总数 4 2 2
1 汉族 2 1 1
2 朝鲜族 1 1 0
3 满族 1 0 1
4 少数民族人口 2 1 1
民族人口比重 50% 50% 50%
------解决方案--------------------
- SQL code
use Tempdbgo--> --> if not object_id(N'Tempdb..#nation') is null drop table #nationGoCreate table #nation([NATION_CODE] int,[NATION_NAME] nvarchar(20))Insert #nationselect 1,N'汉族' union allselect 10,N'朝鲜族' union allselect 19,N'满族'Gouse Tempdbgo--> --> if not object_id(N'Tempdb..#Sex') is null drop table #SexGoCreate table #Sex([Code] int,[CodeName] nvarchar(6))Insert #Sexselect 1,N'男' union allselect 2,N'女' union allselect 0,N'未知的性别' union allselect 9,N'未说明的性别'Goif not object_id(N'Tempdb..#resident') is null drop table #residentGoCreate table #resident([PK_RESIDENT] NVARCHAR(50),[AUTOID] NVARCHAR(50),[FULLNAME] nvarchar(3),[SEX] int,[NATION] int)Insert #residentselect '469029404000617504160','469029404000617504114',N'陈小珍',2,19Go--序号 民族 合计 男 女select 序号=ROW_NUMBER()over(order by groups,case when 民族=N'民族' then 1 else 2 end),民族,合计,男,女from (SELECT ISNULL(a.[NATION_NAME],N'合计总数') AS 民族,rtrim(COUNT(b.[SEX]))AS 合计,rtrim(count(CASE WHEN a.[CodeName]=N'男' THEN b.[SEX] END)) AS 男,rtrim(count(CASE WHEN a.[CodeName]=N'女' THEN b.[SEX] END)) AS 女,case when GROUPING(a.[NATION_NAME])=1 then 0 else 1 end as groupsFROM (SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName] LIKE N'[男女]')aLEFT JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE]GROUP BY a.[NATION_NAME] WITH rollupunion allselect '少数民族人口' as 民族,rtrim(COUNT(b.[SEX]))AS 合计,rtrim(count(CASE WHEN a.[CodeName]=N'男' THEN b.[SEX] END)) AS 男,rtrim(count(CASE WHEN a.[CodeName]=N'女' THEN b.[SEX] END)) AS 女,groups=2from(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName] LIKE N'[男女]')ainner JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE] and a.[NATION_NAME] not in(N'汉族')union allselect '民族人口比重' as 民族,str(sum(case when a.[NATION_NAME]!=N'汉族' then 1 else 0 end)*100.0/sum(1),5,2)+'%' AS 合计,str(sum(CASE WHEN a.[CodeName]=N'男' and a.[NATION_NAME]!=N'汉族' THEN 1 else 0 END)/isnull(nullif(sum(CASE WHEN a.[CodeName]=N'男' THEN 1 else 0 END),0),1),5,2)+'%' AS 男,str(sum(CASE WHEN a.[CodeName]=N'女' and a.[NATION_NAME]!=N'汉族' THEN 1 else 0 END)/isnull(nullif(sum(CASE WHEN a.[CodeName]=N'女' THEN 1 else 0 END),0),1),5,2)+'%' AS 女,groups=3from(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName] LIKE N'[男女]')ainner JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE] )torder by 序号/*序号 民族 合计 男 女1 合计总数 1 0 12 朝鲜族 0 0 03 汉族 0 0 04 满族 1 0 15 少数民族人口 1 0 16 民族人口比重 100.0% 0.00% 1.00%*/
------解决方案--------------------
- SQL code
改显示，百分比那里改改use Tempdbgo--> --> if not object_id(N'Tempdb..#nation') is null drop table #nationGoCreate table #nation([NATION_CODE] int,[NATION_NAME] nvarchar(20))Insert #nationselect 1,N'汉族' union allselect 10,N'朝鲜族' union allselect 19,N'满族'Gouse Tempdbgo--> --> if not object_id(N'Tempdb..#Sex') is null drop table #SexGoCreate table #Sex([Code] int,[CodeName] nvarchar(6))Insert #Sexselect 1,N'男' union allselect 2,N'女' union allselect 0,N'未知的性别' union allselect 9,N'未说明的性别'Goif not object_id(N'Tempdb..#resident') is null drop table #residentGoCreate table #resident([PK_RESIDENT] NVARCHAR(50),[AUTOID] NVARCHAR(50),[FULLNAME] nvarchar(3),[SEX] int,[NATION] int)Insert #residentselect '469029404000617504160','469029404000617504114',N'陈小珍',2,19Goselect 序号=case when sign(序号)=1 then rtrim(ROW_NUMBER()over(partition by sign(序号) order by cast(序号 as int))) else '' end,民族,合计,男,女from (SELECT 序号=case when grouping(a.[NATION_NAME])=1 then '' else rtrim(min(a.[NATION_CODE])) end,ISNULL(a.[NATION_NAME],N'合计总数') AS 民族,rtrim(COUNT(b.[SEX]))AS 合计,rtrim(count(CASE WHEN a.[CodeName]=N'男' THEN b.[SEX] END)) AS 男,rtrim(count(CASE WHEN a.[CodeName]=N'女' THEN b.[SEX] END)) AS 女,case when GROUPING(a.[NATION_NAME])=1 then 0 else 1 end as groupsFROM (SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName] LIKE N'[男女]')aLEFT JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE]GROUP BY a.[NATION_NAME] WITH rollupunion allselect 序号='100000','少数民族人口' as 民族,rtrim(COUNT(b.[SEX]))AS 合计,rtrim(count(CASE WHEN a.[CodeName]=N'男' THEN b.[SEX] END)) AS 男,rtrim(count(CASE WHEN a.[CodeName]=N'女' THEN b.[SEX] END)) AS 女,groups=2from(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName] LIKE N'[男女]')ainner JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE] and a.[NATION_NAME] not in(N'汉族')union allselect 序号='','民族人口比重' as 民族,str(sum(case when a.[NATION_NAME]!=N'汉族' then 1 else 0 end)*100.0/sum(1),5,2)+'%' AS 合计,str(sum(CASE WHEN a.[CodeName]=N'男' and a.[NATION_NAME]!=N'汉族' THEN 1 else 0 END)*100.0/isnull(nullif(sum(CASE WHEN a.[CodeName]=N'男' THEN 1 else 0 END),0),1),5,2)+'%' AS 男,str(sum(CASE WHEN a.[CodeName]=N'女' and a.[NATION_NAME]!=N'汉族' THEN 1 else 0 END)*100.0/isnull(nullif(sum(CASE WHEN a.[CodeName]=N'女' THEN 1 else 0 END),0),1),5,2)+'%' AS 女,groups=3from(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName] LIKE N'[男女]')ainner JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE] )torder by groups,序号/*序号 民族 合计 男 女 合计总数 1 0 11 汉族 0 0 02 朝鲜族 0 0 03 满族 1 0 14 少数民族人口 1 0 1 民族人口比重 100.0% 0.00% 100.0%*/