Oracle查询中使用ORDER BY子句的NULLS LAST选项未按预期工作。

huangapple
go评论86阅读模式
英文:

oracle query with nulls last on order by clause not working as expected

问题

You can achieve the desired output by modifying the ORDER BY clause as follows:

  1. ORDER BY dept_id DESC, avg NULLS LAST, toDate DESC NULLS LAST;

This query will first order the results by dept_id in descending order. Then, it will order by avg with NULLS LAST, which ensures that rows with NULL values in the avg column will appear at the end. Finally, it will further order by toDate in descending order with NULLS LAST, achieving the desired result.

英文:

I have data like this for a department say computer science(CSE) with dept_id = 70. All the rows until the last comes from one table and average for that department comes from a different query with union all.

Pseudo-query:

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

So this query gives results as above. I want to change the query the way its order in such a way that 1st order by dept_id, then order by avg with nulls last and then order by to_date desc.
the average of the dept should be the bottom most. result expected as below:

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

I tries this order by

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

it doesnt have any change. Can i apply 2 sets of nulls last ? how do i achieve the output.

答案1

得分: 1

You can skip the second table and use ROLLUP:

  1. SELECT id,
  2.        MAX(dept) AS dept,
  3.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(name) END AS name,
  4.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(from_date) END AS from_date,
  5.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(to_date) END AS to_date,
  6.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(mi) END AS mi,
  7.        ROUND(AVG(avg), 2) AS avg,
  8.        dept_id
  9. FROM   dept_vw vw
  10. GROUP BY ROLLUP(dept_id, id)
  11. HAVING GROUPING_ID(dept_id, id) IN (0, 1)
  12. ORDER BY
  13.        dept_id,
  14.        GROUPING_ID(dept_id, id),
  15.        from_date DESC;

对于示例数据：

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

输出：

ID DEPT NAME FROM_DATE TO_DATE MI AVG DEPT_ID
12 CSE BRADLEY 2015-04-01 00:00:00 2015-06-25 00:00:00 CH 3.69 70
22 CSE ASHLEY 2015-02-01 00:00:00 2015-06-15 00:00:00 EN null 70
37 CSE KEFFLER 2015-01-01 00:00:00 2015-01-02 00:00:00 CH null 70
35 CSE STRONG 2014-11-27 00:00:00 2015-02-15 00:00:00 CH 4.08 70
25 CSE LYNCH 2014-10-04 00:00:00 2015-02-15 00:00:00 CH 3.08 70
30 CSE GARCES 2014-04-01 00:00:00 2015-02-16 00:00:00 TR 3.77 70
34 CSE STRONG 2014-04-01 00:00:00 2014-10-26 00:00:00 TD 4 70
24 CSE LYNCH 2014-04-01 00:00:00 2014-08-23 00:00:00 TD 3 70
33 CSE STRONG 2014-02-11 00:00:00 2014-03-31 00:00:
英文:

You can skip the second table and use ROLLUP:

  1. SELECT id,
  2.        MAX(dept) AS dept,
  3.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(name) END AS name,
  4.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(from_date) END AS from_date,
  5.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(to_date) END AS to_date,
  6.        CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(mi) END AS mi,
  7.        ROUND(AVG(avg), 2) AS avg,
  8.        dept_id
  9. FROM   dept_vw vw
  10. GROUP BY ROLLUP(dept_id, id)
  11. HAVING GROUPING_ID(dept_id, id) IN (0, 1)
  12. ORDER BY
  13.        dept_id,
  14.        GROUPING_ID(dept_id, id),
  15.        from_date DESC;

Which, for the sample data:

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

Outputs:

ID DEPT NAME FROM_DATE TO_DATE MI AVG DEPT_ID
12 CSE BRADLEY 2015-04-01 00:00:00 2015-06-25 00:00:00 CH 3.69 70
22 CSE ASHLEY 2015-02-01 00:00:00 2015-06-15 00:00:00 EN null 70
37 CSE KEFFLER 2015-01-01 00:00:00 2015-01-02 00:00:00 CH null 70
35 CSE STRONG 2014-11-27 00:00:00 2015-02-15 00:00:00 CH 4.08 70
25 CSE LYNCH 2014-10-04 00:00:00 2015-02-15 00:00:00 CH 3.08 70
30 CSE GARCES 2014-04-01 00:00:00 2015-02-16 00:00:00 TR 3.77 70
34 CSE STRONG 2014-04-01 00:00:00 2014-10-26 00:00:00 TD 4 70
24 CSE LYNCH 2014-04-01 00:00:00 2014-08-23 00:00:00 TD 3 70
33 CSE STRONG 2014-02-11 00:00:00 2014-03-31 00:00:00 AN null 70
23 CSE LYNCH 2014-02-08 00:00:00 2014-03-31 00:00:00 AN null 70
29 CSE GARCES 2013-12-11 00:00:00 2014-03-31 00:00:00 AN 3.54 70
28 CSE DENNEY 2013-09-01 00:00:00 2014-01-13 00:00:00 CH 3.92 70
21 CSE SHI 2013-04-01 00:00:00 2014-01-13 00:00:00 CH 3.69 70
18 CSE SAVOY 2013-04-01 00:00:00 2013-11-14 00:00:00 EN null 70
27 CSE WHITE 2013-04-01 00:00:00 2013-06-28 00:00:00 GC 2 70
17 CSE SAVOY 2013-02-01 00:00:00 2013-03-31 00:00:00 AN 2.54 70
20 CSE SHI 2013-02-01 00:00:00 2013-03-31 00:00:00 AN 3.15 70
26 CSE WHITE 2013-01-01 00:00:00 2013-03-31 00:00:00 AN 2.77 70
null CSE null null null null 3.33 70

fiddle

答案2

得分: 0

你可以添加一个列来指定排序中的优先级：

  1. SELECT id, dept, name, from_date, to_date, mi, avg, dept_id 
  2. FROM   (
  3.   SELECT id , dept, name, from_date, to_date, mi, avg, dept_id, 1 AS priority
  4.   FROM   dept_vw vw 
  5. UNION ALL
  6.   SELECT NULL, dept, NULL, NULL, NULL, NULL, round(avg,2), dept_id, 2
  7.   FROM   avg_vw
  8. ) 
  9. ORDER BY dept_id DESC, priority, to_Date DESC;

对于样本数据：

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

输出：

ID DEPT NAME FROM_DATE TO_DATE MI AVG DEPT_ID
12 CSE BRADLEY 2015-04-01 00:00:00 2015-06-25 00:00:00 CH 3.69 70
22 CSE ASHLEY 2015-02-01 00:00:00 2015-06-15 00:00:00 EN null 70
30 CSE GARCES 2014-04-01 00:00:00 2015-02-16 00:00:00 TR 3.77 70
25 CSE LYNCH 2014-10-04 00:00:00 2015-02-15 00:00:00 CH 3.08 70
35 CSE STRONG 2014-11-27 00:00:00 2015-02-15 00:00:00 CH 4.08 70
37 CSE KEFFLER 2015-01-01 00:00:00 2015-01-02 00:00:00 CH null 70
34 CSE STRONG 2014-04-01 00:00:00 2014-10-26 00:00:00 TD 4 70
24 CSE LYNCH 2014-04-01 00:00:00 2014-08-23 00:00:00 TD 3 70
33 CSE STRONG 2014-02-11 00:00:00 2014-03-31 00:00:00 AN null 70
29 CSE GARCES 2013-12-11 00:00:
英文:

You can add a column to specify the priority in the ordering:

  1. SELECT id, dept, name, from_date, to_date, mi, avg, dept_id 
  2. FROM   (
  3.   SELECT id , dept, name, from_date, to_date, mi, avg, dept_id, 1 AS priority
  4.   FROM   dept_vw vw 
  5. UNION ALL
  6.   SELECT NULL, dept, NULL, NULL, NULL, NULL, round(avg,2), dept_id, 2
  7.   FROM   avg_vw
  8. ) 
  9. ORDER BY dept_id DESC, priority, to_Date DESC;

Which, for the sample data:

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

Outputs:

ID DEPT NAME FROM_DATE TO_DATE MI AVG DEPT_ID
12 CSE BRADLEY 2015-04-01 00:00:00 2015-06-25 00:00:00 CH 3.69 70
22 CSE ASHLEY 2015-02-01 00:00:00 2015-06-15 00:00:00 EN null 70
30 CSE GARCES 2014-04-01 00:00:00 2015-02-16 00:00:00 TR 3.77 70
25 CSE LYNCH 2014-10-04 00:00:00 2015-02-15 00:00:00 CH 3.08 70
35 CSE STRONG 2014-11-27 00:00:00 2015-02-15 00:00:00 CH 4.08 70
37 CSE KEFFLER 2015-01-01 00:00:00 2015-01-02 00:00:00 CH null 70
34 CSE STRONG 2014-04-01 00:00:00 2014-10-26 00:00:00 TD 4 70
24 CSE LYNCH 2014-04-01 00:00:00 2014-08-23 00:00:00 TD 3 70
33 CSE STRONG 2014-02-11 00:00:00 2014-03-31 00:00:00 AN null 70
29 CSE GARCES 2013-12-11 00:00:00 2014-03-31 00:00:00 AN 3.54 70
23 CSE LYNCH 2014-02-08 00:00:00 2014-03-31 00:00:00 AN null 70
21 CSE SHI 2013-04-01 00:00:00 2014-01-13 00:00:00 CH 3.69 70
28 CSE DENNEY 2013-09-01 00:00:00 2014-01-13 00:00:00 CH 3.92 70
18 CSE SAVOY 2013-04-01 00:00:00 2013-11-14 00:00:00 EN null 70
27 CSE WHITE 2013-04-01 00:00:00 2013-06-28 00:00:00 GC 2 70
26 CSE WHITE 2013-01-01 00:00:00 2013-03-31 00:00:00 AN 2.77 70
17 CSE SAVOY 2013-02-01 00:00:00 2013-03-31 00:00:00 AN 2.54 70
20 CSE SHI 2013-02-01 00:00:00 2013-03-31 00:00:00 AN 3.15 70
null CSE null null null null 3.31 70

fiddle

huangapple
  • 本文由 发表于 2023年5月25日 21:09:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/76332655.html
  • oracle
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定