今天写sql遇到一个小小的问题,聚合函数,排序,限制返回总行数,因为是oracle数据库,所以不能像mysql数据库那样简便,先放出我顿悟下的语句吧
select *
from(
select
name,
sum(price)
from temp
group by name
order by sum(price) desc
)
where rownum < 31
这里用的是“外包”哈哈,即先把排序结果作为一个临时表,然后外面进行限制返回总行数,然而我居然最开始没想到
思路
我的思路轨迹,最开始想用rownum的,类似于这样:
SELECT A,
SUM(B)
FROM C
WHERE ROWNUM < 31
GROUP BY A
ORDER BY SUM(B)
但是报错了,因为
- ROWNUM 是在行被检索出后赋值的,而不是在查询的开始时就进行赋值。这意味着在 WHERE 子句中使用 ROWNUM 进行结果限制时,它会应用于已经检索出的行,而不会考虑查询结果的最终顺序。因此,直接使用 ROWNUM < 31 是不正确的,因为它无法保证返回的是最小的 30 行。
- WHERE 子句执行在表格访问之前,这意味着 ROWNUM 还没有被分配给行。因此,如果在 WHERE 子句中使用 ROWNUM 进行过滤,将返回空结果集,因为没有行被分配给 ROWNUM
然后了解到了fetch分页子句,于是新的sql诞生了
SELECT A, SUM(B)
FROM C
GROUP BY A
ORDER BY SUM(B)
FETCH FIRST 30 ROWS ONLY;
还是不行,因为公司oracle版本问题(大概率是),这就很烦咯
接着就考虑子查询配合rownum行不行,sql开写
SELECT A, SUM(B)
FROM (
SELECT A, B, ROWNUM AS rn
FROM (
SELECT A, B
FROM C
ORDER BY B
)
)
WHERE rn <= 30
GROUP BY A
ORDER BY SUM(B);
SELECT A, SUM(B)
FROM (
SELECT A, B, ROW_NUMBER() OVER (ORDER BY B) AS rn
FROM C
)
WHERE rn <= 30
GROUP BY A
ORDER BY SUM(B);
这两种思路的误区在于执着rownum给分配一个伪列序号,然后根据这个序号再来排序,貌似合理但是却是对rownum不够理解透彻的原因,或者说不够灵活变通,后面去接杯水,一喝就想到了其他不一样的方法
参考链接
最后也是去了csdn小小的了解学习了一下,两个很有参考价值的链接,上班时间写博客哈哈哈,恕我不能提供更多的讲解,有时间再来细致学习一遍
http://t.csdn.cn/N6gL3
http://t.csdn.cn/S518F