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(3))
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 
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'[男女]')a
LEFT 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 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'男'