select address_sheng,Shengsum (select *,rn=row_number() over (order by Shengsum desc) from (select SUBSTRING(address_sheng,1,2) address_sheng ,count(address_sheng) Shengsum from Kehu_Info WHERE hezuoZt='1' group by address_sheng) A) B where rn<=5 union all select '其他',sum(Shengsum) from select *,rn=row_number() over (order by Shengsum desc) from (select SUBSTRING(address_sheng,1,2) address_sheng ,count(address_sheng) Shengsum from Kehu_Info WHERE hezuoZt='1' group by address_sheng) A) B where rn>5
试试这个: with cte1 as ( select '黑龙' address_sheng,1042 Shengsum union all select '辽宁' address_sheng,816 Shengsum union all select '山东' address_sheng,718 Shengsum union all select '河北' address_sheng,463 Shengsum union all select '内蒙' address_sheng,427 Shengsum union all select '吉林' address_sheng,356 Shengsum union all select '山西' address_sheng,126 Shengsum union all select '北京' address_sheng,119 Shengsum union all select '河南' address_sheng,108 Shengsum union all select '湖南' address_sheng,105 Shengsum union all select '陕西' address_sheng,93 Shengsum union all select '江苏' address_sheng,90 Shengsum union all select '湖北' address_sheng,50 Shengsum union all select '云南' address_sheng,47 Shengsum union all select '四川' address_sheng,42 Shengsum union all select '重庆' address_sheng,40 Shengsum union all select '甘肃' address_sheng,37 Shengsum union all select '安徽' address_sheng,33 Shengsum union all select '江西' address_sheng,2 Shengsum union all select '天津' address_sheng,2 Shengsum ) select * from (select top 5 * from cte1 order by Shengsum desc) aa union all select '其他', SUM(shengsum) from cte1 where address_sheng not in (select top 5 address_sheng from cte1 order by Shengsum)
上面的那个有点错误,用这个: with cte1 as ( select '黑龙' address_sheng,1042 Shengsum union all select '辽宁' address_sheng,816 Shengsum union all select '山东' address_sheng,718 Shengsum union all select '河北' address_sheng,463 Shengsum union all select '内蒙' address_sheng,427 Shengsum union all select '吉林' address_sheng,356 Shengsum union all select '山西' address_sheng,126 Shengsum union all select '北京' address_sheng,119 Shengsum union all select '河南' address_sheng,108 Shengsum union all select '湖南' address_sheng,105 Shengsum union all select '陕西' address_sheng,93 Shengsum union all select '江苏' address_sheng,90 Shengsum union all select '湖北' address_sheng,50 Shengsum union all select '云南' address_sheng,47 Shengsum union all select '四川' address_sheng,42 Shengsum union all select '重庆' address_sheng,40 Shengsum union all select '甘肃' address_sheng,37 Shengsum union all select '安徽' address_sheng,33 Shengsum union all select '江西' address_sheng,2 Shengsum union all select '天津' address_sheng,2 Shengsum ) select * from (select top 5 * from cte1 order by Shengsum desc) aa union all select '其他', SUM(shengsum) from cte1 where address_sheng not in (select top 5 address_sheng from cte1 order by Shengsum desc)
(select *,rn=row_number() over (order by Shengsum desc) from
(select SUBSTRING(address_sheng,1,2) address_sheng ,count(address_sheng) Shengsum from Kehu_Info
WHERE hezuoZt='1' group by address_sheng) A) B where rn<=5
union all
select '其他',sum(Shengsum) from select *,rn=row_number() over (order by Shengsum desc) from
(select SUBSTRING(address_sheng,1,2) address_sheng ,count(address_sheng) Shengsum from Kehu_Info
WHERE hezuoZt='1' group by address_sheng) A) B where rn>5
with cte1 as
(
select '黑龙' address_sheng,1042 Shengsum union all
select '辽宁' address_sheng,816 Shengsum union all
select '山东' address_sheng,718 Shengsum union all
select '河北' address_sheng,463 Shengsum union all
select '内蒙' address_sheng,427 Shengsum union all
select '吉林' address_sheng,356 Shengsum union all
select '山西' address_sheng,126 Shengsum union all
select '北京' address_sheng,119 Shengsum union all
select '河南' address_sheng,108 Shengsum union all
select '湖南' address_sheng,105 Shengsum union all
select '陕西' address_sheng,93 Shengsum union all
select '江苏' address_sheng,90 Shengsum union all
select '湖北' address_sheng,50 Shengsum union all
select '云南' address_sheng,47 Shengsum union all
select '四川' address_sheng,42 Shengsum union all
select '重庆' address_sheng,40 Shengsum union all
select '甘肃' address_sheng,37 Shengsum union all
select '安徽' address_sheng,33 Shengsum union all
select '江西' address_sheng,2 Shengsum union all
select '天津' address_sheng,2 Shengsum
)
select * from (select top 5 * from cte1 order by Shengsum desc) aa
union all
select '其他', SUM(shengsum) from cte1 where address_sheng not in (select top 5 address_sheng from cte1 order by Shengsum)
with cte1 as
(
select '黑龙' address_sheng,1042 Shengsum union all
select '辽宁' address_sheng,816 Shengsum union all
select '山东' address_sheng,718 Shengsum union all
select '河北' address_sheng,463 Shengsum union all
select '内蒙' address_sheng,427 Shengsum union all
select '吉林' address_sheng,356 Shengsum union all
select '山西' address_sheng,126 Shengsum union all
select '北京' address_sheng,119 Shengsum union all
select '河南' address_sheng,108 Shengsum union all
select '湖南' address_sheng,105 Shengsum union all
select '陕西' address_sheng,93 Shengsum union all
select '江苏' address_sheng,90 Shengsum union all
select '湖北' address_sheng,50 Shengsum union all
select '云南' address_sheng,47 Shengsum union all
select '四川' address_sheng,42 Shengsum union all
select '重庆' address_sheng,40 Shengsum union all
select '甘肃' address_sheng,37 Shengsum union all
select '安徽' address_sheng,33 Shengsum union all
select '江西' address_sheng,2 Shengsum union all
select '天津' address_sheng,2 Shengsum
)
select * from (select top 5 * from cte1 order by Shengsum desc) aa
union all
select '其他', SUM(shengsum) from cte1 where address_sheng not in (select top 5 address_sheng from cte1 order by Shengsum desc)
原始数据不是上面的数据,上面的数据是我的执行另外一条语句的结果原始数据是这样的 ,先要统计出来每个地区出现的次数 然后在 调出前5名 剩下的显示成其他
id address_sheng
1 黑龙
2 黑龙
3 山东
4 河北
5 河北
6 吉林
7 山东
8 河北
9 河北
10 吉林
11 山东