Oracle查询last on order by子句为null，未按预期工作

ghhkc1vu  于 2023-05-28  发布在  Oracle
关注(0)|答案(2)|浏览(264)

我有这样一个部门的数据，比如计算机科学（CSE），dept_id = 70。直到最后一行的所有行都来自一个表，该部门的平均值来自一个使用union all的不同查询。
伪查询：

SELECT id, dept, name, from_date, to_date, mi, avg, dept_id 
FROM (SELECT id , dept, name, from_date, to_date, mi, avg, dept_id  
      FROM dept_vw vw 
      
      UNION

      SELECT NULL AS id, dept, NULL AS name, NULL AS fromDate, NULL AS toDate, null AS mi, round(avg,2) AS avg, dept_id
      FROM avg_vw) 
ORDER BY dept_id DESC, toDate DESC NULLS LAST;
id  dept name   fromDate    toDate      mi  avg dept_id
12  CSE BRADLEY 01-APR-15   25-JUN-15   CH  3.69    70
22  CSE ASHLEY  01-FEB-15   15-JUN-15   EN      70
30  CSE GARCES  01-APR-14   16-FEB-15   TR  3.77    70
25  CSE LYNCH   04-OCT-14   15-FEB-15   CH  3.08    70
35  CSE STRONG  27-NOV-14   15-FEB-15   CH  4.08    70
37  CSE KEFFLER 01-JAN-15   02-JAN-15   CH      70
34  CSE STRONG  01-APR-14   26-OCT-14   TD  4   70
24  CSE LYNCH   01-APR-14   23-AUG-14   TD  3   70
33  CSE STRONG  11-FEB-14   31-MAR-14   AN      70
29  CSE GARCES  11-DEC-13   31-MAR-14   AN  3.54    70
23  CSE LYNCH   08-FEB-14   31-MAR-14   AN      70
21  CSE SHI     01-APR-13   13-JAN-14   CH  3.69    70
28  CSE DENNEY  01-SEP-13   13-JAN-14   CH  3.92    70
18  CSE SAVOY   01-APR-13   14-NOV-13   EN      70
27  CSE WHITE   01-APR-13   28-JUN-13   GC  2   70
26  CSE WHITE   01-JAN-13   31-MAR-13   AN  2.77    70
17  CSE SAVOY   01-FEB-13   31-MAR-13   AN  2.54    70
20  CSE SHI 01-FEB-13   31-MAR-13   AN  3.15    70
42  CSE                         3.31    70

因此，该查询给出如上所述的结果。我想改变查询的方式，其顺序在这样一种方式，第一秩序的dept_id，然后秩序的平均值与null最后，然后秩序的to_date desc。部门的平均值应该是最底部的。预期结果如下：

id  dept name   fromDate    toDate      mi  avg dept_id
12  CSE BRADLEY 01-APR-15   25-JUN-15   CH  3.69    70
30  CSE GARCES  01-APR-14   16-FEB-15   TR  3.77    70
25  CSE LYNCH   04-OCT-14   15-FEB-15   CH  3.08    70
35  CSE STRONG  27-NOV-14   15-FEB-15   CH  4.08    70
34  CSE STRONG  01-APR-14   26-OCT-14   TD  4   70
24  CSE LYNCH   01-APR-14   23-AUG-14   TD  3   70
29  CSE GARCES  11-DEC-13   31-MAR-14   AN  3.54    70
21  CSE SHI     01-APR-13   13-JAN-14   CH  3.69    70
28  CSE DENNEY  01-SEP-13   13-JAN-14   CH  3.92    70
27  CSE WHITE   01-APR-13   28-JUN-13   GC  2   70
26  CSE WHITE   01-JAN-13   31-MAR-13   AN  2.77    70
17  CSE SAVOY   01-FEB-13   31-MAR-13   AN  2.54    70
20  CSE SHI     01-FEB-13   31-MAR-13   AN  3.15    70
22  CSE ASHLEY  01-FEB-15   15-JUN-15   EN      70
37  CSE KEFFLER 01-JAN-15   02-JAN-15   CH      70
33  CSE STRONG  11-FEB-14   31-MAR-14   AN          70
23  CSE LYNCH   08-FEB-14   31-MAR-14   AN      70
18  CSE SAVOY   01-APR-13   14-NOV-13   EN      70
42  CSE                         3.31    70

我尝试这个顺序

'order by dept_id desc, toDate desc nulls last, avg nulls last'

没有零钱。我可以最后应用两组空值吗？我如何实现输出。

oracle

2条答案

按热度按时间
mmvthczy

mmvthczy1#

您可以跳过第二个表，使用ROLLUP：

SELECT id,
       MAX(dept) AS dept,
       CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(name) END AS name,
       CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(from_date) END AS from_date,
       CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(to_date) END AS to_date,
       CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(mi) END AS mi,
       ROUND(AVG(avg), 2) AS avg,
       dept_id
FROM   dept_vw vw
GROUP BY ROLLUP(dept_id, id)
HAVING GROUPING_ID(dept_id, id) IN (0, 1)
ORDER BY
       dept_id,
       GROUPING_ID(dept_id, id),
       from_date DESC;

其中，对于样本数据：

CREATE TABLE dept_vw (id, dept, name, from_Date, to_Date, mi, avg, dept_id) AS
SELECT 12, 'CSE', 'BRADLEY', DATE '2015-04-01', DATE '2015-06-25', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 22, 'CSE', 'ASHLEY',  DATE '2015-02-01', DATE '2015-06-15', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 30, 'CSE', 'GARCES',  DATE '2014-04-01', DATE '2015-02-16', 'TR', 3.77, 70 FROM DUAL UNION ALL
SELECT 25, 'CSE', 'LYNCH',   DATE '2014-10-04', DATE '2015-02-15', 'CH', 3.08, 70 FROM DUAL UNION ALL
SELECT 35, 'CSE', 'STRONG',  DATE '2014-11-27', DATE '2015-02-15', 'CH', 4.08, 70 FROM DUAL UNION ALL
SELECT 37, 'CSE', 'KEFFLER', DATE '2015-01-01', DATE '2015-01-02', 'CH', NULL, 70 FROM DUAL UNION ALL
SELECT 34, 'CSE', 'STRONG',  DATE '2014-04-01', DATE '2014-10-26', 'TD', 4,    70 FROM DUAL UNION ALL
SELECT 24, 'CSE', 'LYNCH',   DATE '2014-04-01', DATE '2014-08-23', 'TD', 3,    70 FROM DUAL UNION ALL
SELECT 33, 'CSE', 'STRONG',  DATE '2014-02-11', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 29, 'CSE', 'GARCES',  DATE '2013-12-11', DATE '2014-03-31', 'AN', 3.54, 70 FROM DUAL UNION ALL
SELECT 23, 'CSE', 'LYNCH',   DATE '2014-02-08', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 21, 'CSE', 'SHI',     DATE '2013-04-01', DATE '2014-01-13', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 28, 'CSE', 'DENNEY',  DATE '2013-09-01', DATE '2014-01-13', 'CH', 3.92, 70 FROM DUAL UNION ALL
SELECT 18, 'CSE', 'SAVOY',   DATE '2013-04-01', DATE '2013-11-14', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 27, 'CSE', 'WHITE',   DATE '2013-04-01', DATE '2013-06-28', 'GC', 2,    70 FROM DUAL UNION ALL
SELECT 26, 'CSE', 'WHITE',   DATE '2013-01-01', DATE '2013-03-31', 'AN', 2.77, 70 FROM DUAL UNION ALL
SELECT 17, 'CSE', 'SAVOY',   DATE '2013-02-01', DATE '2013-03-31', 'AN', 2.54, 70 FROM DUAL UNION ALL
SELECT 20, 'CSE', 'SHI',     DATE '2013-02-01', DATE '2013-03-31', 'AN', 3.15, 70 FROM DUAL;

输出：
| ID| DEPT|名称|开始日期|截止日期|MI|平均值|部门ID|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 十二岁|CSE|布拉德利|2015-04-01 00：00：00| 2015-06-25 00：00：00| CH| 3.69|七十|
| 二十二|CSE|阿什利|2015-02-01 00：00：00| 2015-06-15 00：00：00| EN|联系我们|七十|
| 三十七|CSE|凯夫勒|2015-01-01 00：00：00| 2015-01-02 00：00：00| CH|联系我们|七十|
| 三十五|CSE|强大|2014-11-27 00：00：00| 2015-02-15 00：00：00| CH|四点零八分|七十|
| 二十五|CSE|林奇|2014-10-04 00：00：00| 2015-02-15 00：00：00| CH|三点零八分|七十|
| 三十|CSE|加尔塞斯|2014-04-01 00：00：00| 2015-02-16 00：00：00| TR|三点七七|七十|
| 三十四|CSE|强大|2014-04-01 00：00：00| 2014-10-26 00：00：00| TD| 4|七十|
| 二十四|CSE|林奇|2014-04-01 00：00：00| 2014-08-23 00：00：00| TD| 3|七十|
| 三十三|CSE|强大|2014-02-11 00：00：00| 2014-03-31 00：00：00| AN|联系我们|七十|
| 二十三|CSE|林奇|2014-02-08 00：00：00| 2014-03-31 00：00：00| AN|联系我们|七十|
| 二十九|CSE|加尔塞斯|2013-12-11 00：00：00| 2014-03-31 00：00：00| AN|三点五四|七十|
| 二十八|CSE|丹尼|2013-09-01 00：00：00| 2014-01-13 00：00：00| CH|三点九二|七十|
| 二十一|CSE| SHI| 2013-04-01 00：00：00| 2014-01-13 00：00：00| CH| 3.69|七十|
| 十八岁|CSE|萨沃伊|2013-04-01 00：00：00| 2013-11-14 00：00：00| EN|联系我们|七十|
| 二十七|CSE|白色|2013-04-01 00：00：00| 2013-06-28 00：00：00|气相色谱|2|七十|
| 十七岁|CSE|萨沃伊|2013-02-01 00：00：00| 2013-03-31 00：00：00| AN| 2.54|七十|
| 二十个|CSE| SHI| 2013-02-01 00：00：00| 2013-03-31 00：00：00| AN| 3.15|七十|
| 二十六|CSE|白色|2013-01-01 00：00：00| 2013-03-31 00：00：00| AN| 2.77|七十|
| 联系我们|CSE|联系我们|联系我们|联系我们|联系我们|三点三三|七十|
fiddle

赞(0）分享  回复(0）举报  2023-05-28
lvjbypge

lvjbypge2#

您可以添加列以指定排序中的优先级：

SELECT id, dept, name, from_date, to_date, mi, avg, dept_id 
FROM   (
  SELECT id , dept, name, from_date, to_date, mi, avg, dept_id, 1 AS priority
  FROM   dept_vw vw 
UNION ALL
  SELECT NULL, dept, NULL, NULL, NULL, NULL, round(avg,2), dept_id, 2
  FROM   avg_vw
) 
ORDER BY dept_id DESC, priority, to_Date DESC;

其中，对于样本数据：

CREATE TABLE dept_vw (id, dept, name, from_Date, to_Date, mi, avg, dept_id) AS
SELECT 12, 'CSE', 'BRADLEY', DATE '2015-04-01', DATE '2015-06-25', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 22, 'CSE', 'ASHLEY',  DATE '2015-02-01', DATE '2015-06-15', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 30, 'CSE', 'GARCES',  DATE '2014-04-01', DATE '2015-02-16', 'TR', 3.77, 70 FROM DUAL UNION ALL
SELECT 25, 'CSE', 'LYNCH',   DATE '2014-10-04', DATE '2015-02-15', 'CH', 3.08, 70 FROM DUAL UNION ALL
SELECT 35, 'CSE', 'STRONG',  DATE '2014-11-27', DATE '2015-02-15', 'CH', 4.08, 70 FROM DUAL UNION ALL
SELECT 37, 'CSE', 'KEFFLER', DATE '2015-01-01', DATE '2015-01-02', 'CH', NULL, 70 FROM DUAL UNION ALL
SELECT 34, 'CSE', 'STRONG',  DATE '2014-04-01', DATE '2014-10-26', 'TD', 4,    70 FROM DUAL UNION ALL
SELECT 24, 'CSE', 'LYNCH',   DATE '2014-04-01', DATE '2014-08-23', 'TD', 3,    70 FROM DUAL UNION ALL
SELECT 33, 'CSE', 'STRONG',  DATE '2014-02-11', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 29, 'CSE', 'GARCES',  DATE '2013-12-11', DATE '2014-03-31', 'AN', 3.54, 70 FROM DUAL UNION ALL
SELECT 23, 'CSE', 'LYNCH',   DATE '2014-02-08', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 21, 'CSE', 'SHI',     DATE '2013-04-01', DATE '2014-01-13', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 28, 'CSE', 'DENNEY',  DATE '2013-09-01', DATE '2014-01-13', 'CH', 3.92, 70 FROM DUAL UNION ALL
SELECT 18, 'CSE', 'SAVOY',   DATE '2013-04-01', DATE '2013-11-14', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 27, 'CSE', 'WHITE',   DATE '2013-04-01', DATE '2013-06-28', 'GC', 2,    70 FROM DUAL UNION ALL
SELECT 26, 'CSE', 'WHITE',   DATE '2013-01-01', DATE '2013-03-31', 'AN', 2.77, 70 FROM DUAL UNION ALL
SELECT 17, 'CSE', 'SAVOY',   DATE '2013-02-01', DATE '2013-03-31', 'AN', 2.54, 70 FROM DUAL UNION ALL
SELECT 20, 'CSE', 'SHI',     DATE '2013-02-01', DATE '2013-03-31', 'AN', 3.15, 70 FROM DUAL;

CREATE TABLE avg_vw (dept, avg, dept_id) AS
SELECT 'CSE', 3.31, 70 FROM DUAL;

输出：
| ID| DEPT|名称|开始日期|截止日期|MI|平均值|部门ID|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 十二岁|CSE|布拉德利|2015-04-01 00：00：00| 2015-06-25 00：00：00| CH| 3.69|七十|
| 二十二|CSE|阿什利|2015-02-01 00：00：00| 2015-06-15 00：00：00| EN|联系我们|七十|
| 三十|CSE|加尔塞斯|2014-04-01 00：00：00| 2015-02-16 00：00：00| TR|三点七七|七十|
| 二十五|CSE|林奇|2014-10-04 00：00：00| 2015-02-15 00：00：00| CH|三点零八分|七十|
| 三十五|CSE|强大|2014-11-27 00：00：00| 2015-02-15 00：00：00| CH|四点零八分|七十|
| 三十七|CSE|凯夫勒|2015-01-01 00：00：00| 2015-01-02 00：00：00| CH|联系我们|七十|
| 三十四|CSE|强大|2014-04-01 00：00：00| 2014-10-26 00：00：00| TD| 4|七十|
| 二十四|CSE|林奇|2014-04-01 00：00：00| 2014-08-23 00：00：00| TD| 3|七十|
| 三十三|CSE|强大|2014-02-11 00：00：00| 2014-03-31 00：00：00| AN|联系我们|七十|
| 二十九|CSE|加尔塞斯|2013-12-11 00：00：00| 2014-03-31 00：00：00| AN|三点五四|七十|
| 二十三|CSE|林奇|2014-02-08 00：00：00| 2014-03-31 00：00：00| AN|联系我们|七十|
| 二十一|CSE| SHI| 2013-04-01 00：00：00| 2014-01-13 00：00：00| CH| 3.69|七十|
| 二十八|CSE|丹尼|2013-09-01 00：00：00| 2014-01-13 00：00：00| CH|三点九二|七十|
| 十八岁|CSE|萨沃伊|2013-04-01 00：00：00| 2013-11-14 00：00：00| EN|联系我们|七十|
| 二十七|CSE|白色|2013-04-01 00：00：00| 2013-06-28 00：00：00|气相色谱|2|七十|
| 二十六|CSE|白色|2013-01-01 00：00：00| 2013-03-31 00：00：00| AN| 2.77|七十|
| 十七岁|CSE|萨沃伊|2013-02-01 00：00：00| 2013-03-31 00：00：00| AN| 2.54|七十|
| 二十个|CSE| SHI| 2013-02-01 00：00：00| 2013-03-31 00：00：00| AN| 3.15|七十|
| 联系我们|CSE|联系我们|联系我们|联系我们|联系我们|3.31|七十|
fiddle

赞(0）分享  回复(0）举报  2023-05-28
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com