：用sql做民族人口构成的统计报表（真的不知道如何回复帖子）

use Tempdbgo--> -->  if not object_id(N'Tempdb..#nation') is null    drop table #nationGoCreate table #nation([NATION_CODE] int,[NATION_NAME] nvarchar(3))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 ISNULL(a.[NATION_NAME],N'總計') AS 民族,COUNT(1)AS 合计,sum(CASE WHEN a.[CodeName]=N'男' THEN 1 ELSE 0 END) AS 男,sum(CASE WHEN a.[CodeName]=N'男' THEN 1 ELSE 0 END) AS 女FROM (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 rollup
------解决方案--------------------
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%*/