use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#nation') is null
    drop table #nation
Go
Create table #nation([NATION_CODE] int,[NATION_NAME] nvarchar(20))
Insert #nation
select 1,N'汉族' union all
select 10,N'朝鲜族' union all
select 19,N'满族'
Go
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#Sex') is null
    drop table #Sex
Go
Create table #Sex([Code] int,[CodeName] nvarchar(6))
Insert #Sex
select 1,N'男' union all
select 2,N'女' union all
select 0,N'未知的性别' union all
select 9,N'未说明的性别'
Go

if not object_id(N'Tempdb..#resident') is null
    drop table #resident
Go
Create table #resident([PK_RESIDENT] NVARCHAR(50),[AUTOID] NVARCHAR(50),[FULLNAME] nvarchar(3),[SEX] int,[NATION] int)
Insert #resident
select '469029404000617504160','469029404000617504114',N'陈小珍',2,19
Go

--序号 民族 合计 男 女

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 groups
FROM 
(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName]  LIKE N'[男女]')a
LEFT JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE]
GROUP BY a.[NATION_NAME] WITH rollup

union all
select '少数民族人口' 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=2
from
(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName]  LIKE N'[男女]')a
inner JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE] and a.[NATION_NAME] not in(N'汉族')
union all
select '民族人口比重' 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=3
from
(SELECT * FROM #nation AS a,#Sex AS b WHERE b.[CodeName]  LIKE N'[男女]')a
inner JOIN #resident AS b ON b.[SEX]=a.[Code] AND b.[NATION]=a.[NATION_CODE] 
)t
order by 序号

/*
序号    民族    合计    男    女
1    合计总数    1    0    1
2    朝鲜族    0    0    0
3    汉族    0    0    0
4    满族    1    0    1
5    少数民族人口    1    0    1
6    民族人口比重    100.0%     0.00%     1.00%
*/

------解决方案--------------------
SQL code
create table resident(PK_RESIDENT varchar(30),AUTOID varchar(30),FULLNAME nvarchar(20),SEX int,NATION int)
insert into resident select '469029404000617504160','469029404000617504114','陈小珍',2,19
insert into resident select 'asdf','fasd','aaa',1,1
insert into resident select 'afqwe','fsadf','bbb',2,10
insert into resident select 'twf','rs','ccc',1,11
insert into resident select 'roijna','fiuasd','ddd',2,15
insert into resident select 'roijna','fiuasd','ddd',1,1
create 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,Code