详细信息

您现在的位置：网站首页 >> 百科问答 >> 详细信息

SQL分组取每组前几条记录，剩下的列为其他

特价活动:>>>> 畅云管家新购、续费8折优惠，畅捷通T+cloud、好会计、易代账、好业财、好生意云产品8折优惠。　

/****** SQLserver 写法 ******/
SELECT *
FROM [lx].[dbo].[全国销售额] where province='北京' --group by [province],[name]

--按销售额排序取每个省的前4名,剩下的为其他
select * from (
select a.province,a.name ,a.value from 全国销售额 a where value in (select top 4 value from 全国销售额 where province=a.province order by value desc)
union all
select a.province,'其他',sum(a.value) value from 全国销售额 a where value not in (select top 4 value from 全国销售额 where province=a.province order by value desc)
group by a.province

) a order by a.province,a.value desc

1

2

3

4

5

6

7

8

9

--oracle 写法（大于5的列到其他）<br>SELECT a.bm,'其他' mdd ,a.jssj,sum(b.jp) jp,sum(b.hc) hc ,sum(b.bzfy) bzfy,sum(b.zsfy) zsfy,sum(b.jtqt) jtqt,sum(b.qt) qt,sum(b.jtgj) jtgj,

sum(b.bmj) bmj,sum(b.ygj) ygj ,sum(b.gsj) gsj,sum(b.zjj) zjj,sum(b.ccts) ccts ,sum(b.shje) shje FROM

(

SELECT a.bm,a.mdd,a.jssj,jtgj, ROW_NUMBER() OVER(PARTITION BY a.bm,a.jssj

ORDER BY jtgj desc) AS RN FROM NSC_OA_CLHZ a

) a left join NSC_OA_CLHZ b on nvl(a.bm,0) = nvl(b.bm,0) and nvl(a.mdd,0) = nvl(b.mdd,0) and nvl(a.jssj,0)=nvl(b.jssj,0)

and a.RN >5

group by a.bm,a.jssj

上一篇:VM虚拟机无法拖拽、粘贴、复制

下一篇:Oracle 数据库备份与还原（Centos 7）