求高手指教：用sql做民族人口构成的统计报表
库为bt_201110
人口表 resident里有民族表 nation和性别表 sex，
表nation结构为：
NATION_CODENATION_NAME
1 汉族
10 朝鲜族
11 满族
表sex结构为：
CodeCodeName
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%
[解决办法]
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%*/
[解决办法]
改显示，百分比那里改改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%*/
[解决办法]
楼主怎么发那么多重复帖...
create table resident(PK_RESIDENT varchar(30),AUTOID varchar(30),FULLNAME nvarchar(20),SEX int,NATION int)insert into resident select '469029404000617504160','469029404000617504114','陈小珍',2,19insert into resident select 'asdf','fasd','aaa',1,1insert into resident select 'afqwe','fsadf','bbb',2,10insert into resident select 'twf','rs','ccc',1,11insert into resident select 'roijna','fiuasd','ddd',2,15insert into resident select 'roijna','fiuasd','ddd',1,1create table nation(NATION_CODE int,NATION_NAME nvarchar(10))insert into nation select 1,'汉族'insert into nation select 10,'朝鲜族'insert into nation select 11,'满族'create table sex(Code int,CodeName nvarchar(10))insert into sex select 1,'男 ' insert into sex select 2,'女' insert into sex select 0,'未知的性别' insert into sex select 9,'未说明的性别'go;with c1 as(select '' as 序号,'合计总数' as 民族,ltrim(count(*)) as 合计, ltrim(sum(case when c.CodeName='男' then 1 else 0 end)) as 男,ltrim(sum(case when c.CodeName='女' then 1 else 0 end)) as 女from resident a inner join sex c on a.sex=c.code),c2 as(select ltrim(b.nation_code)序号,b.nation_name,ltrim(count(*)) 合计,ltrim(sum(case when c.CodeName='男' then 1 else 0 end)) as 男,ltrim(sum(case when c.CodeName='女' then 1 else 0 end)) as 女from resident a inner join nation b on a.nation=b.nation_codeinner join sex c on a.sex=c.codegroup by b.nation_code,b.nation_name),c3 as(select '' 序号,'少数民族人口' 民族,ltrim(count(*)) 合计,ltrim(sum(case when c.CodeName='男' then 1 else 0 end)) as 男,ltrim(sum(case when c.CodeName='女' then 1 else 0 end)) as 女from resident a inner join nation b on a.nation=b.nation_codeinner join sex c on a.sex=c.codewhere b.Nation_name<>'汉族')select * from c1union allselect * from c2union allselect * from c3union allselect '','民族人口比重',ltrim(floor(c3.合计*100./c1.合计))+'%',ltrim(floor(c3.男*100./c1.男))+'%',ltrim(floor(c3.女*100./c1.女))+'%'from c1,c3/*序号 民族 合计 男 女----------- ------------ ------------------------------------------ ------------------------------------------ ------------------------------------------ 合计总数 6 3 31 汉族 2 2 010 朝鲜族 1 0 111 满族 1 1 0 少数民族人口 2 1 1 民族人口比重 33% 33% 33%(6 行受影响)*/godrop table resident,nation,sex
