英文:
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:
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:
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
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:
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
I tries this order by
'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:
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 | 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:
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;
Which, for the sample data:
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;
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 |
答案2
得分: 0
你可以添加一个列来指定排序中的优先级：
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 | 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:
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;
Which, for the sample data:
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;
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论