表a
----------------------
id sid date job_t
1 111 20110711 5
2 111 20110711 7
3 113 20110712 18
4 113 20110713 23
-----------------------表b
-----------------------
id sid date job_o
1 111 20110711 12
2 111 20110711 5
3 114 20110714 7
4 114 20110714 9
-----------------------表c
-----------------------
sid sname
111 李一
112 李二
113 李三
114 李四
115 李五 通过select语句要产生如下结果的表:
sid sname job_t_sum job_o_sum date
111 李一 5+7 12+5 20110711
113 李三 18 0 20110712
113 李三 23 0 20110713
114 李四 0 7+9 20110714
----------------------
id sid date job_t
1 111 20110711 5
2 111 20110711 7
3 113 20110712 18
4 113 20110713 23
-----------------------表b
-----------------------
id sid date job_o
1 111 20110711 12
2 111 20110711 5
3 114 20110714 7
4 114 20110714 9
-----------------------表c
-----------------------
sid sname
111 李一
112 李二
113 李三
114 李四
115 李五 通过select语句要产生如下结果的表:
sid sname job_t_sum job_o_sum date
111 李一 5+7 12+5 20110711
113 李三 18 0 20110712
113 李三 23 0 20110713
114 李四 0 7+9 20110714
SELECT T1.SID, C.SNAME, T1.JOB_T_SUM, T2.JOB_O_SUM, T1.DATE
FROM (SELECT SID, DATE, SUM(JOB_T) AS JOB_T_SUM FROM A) T1,
(SELECT SID, DATE, SUM(JOB_O) AS JOB_O_SUM FROM B) T2,
C
WHERE T1.DATE = T2.DATE
AND T1.SID = T2.SID
AND T2.SID = C.SID;
SELECT c.sid, c.sname, d.job_t_sum, d.job_o_sum, DATE
FROM (SELECT nvl(a.sid, b.sid) sid,
SUM(job_t) jot_t_sum,
SUM(job_o),
nvl(a.date, b.date) DATE job_o_sum
FROM a
FULL OUTER JOIN b
ON (a.id = b.sid AND a.date = b.date)
GROUP BY nvl(a.sid, b.sid)) d,
c
WHERE d.sid = c.sid;
SELECT '1' tid,'111' sid,'20110711' adate, 5 job_t FROM DUAL UNION ALL
SELECT '2' tid,'111' sid,'20110711' adate, 7 job_t FROM DUAL UNION ALL
SELECT '3' tid,'113' sid,'20110712' adate, 18 job_t FROM DUAL UNION ALL
SELECT '4' tid,'113' sid,'20110713' adate, 23 job_t FROM DUAL
),b AS (
SELECT '1' tid,'111' sid,'20110711' adate, 12 job_o FROM DUAL UNION ALL
SELECT '2' tid,'111' sid,'20110711' adate, 5 job_o FROM DUAL UNION ALL
SELECT '3' tid,'114' sid,'20110714' adate, 7 job_o FROM DUAL UNION ALL
SELECT '4' tid,'114' sid,'20110714' adate, 9 job_o FROM DUAL
),c AS (
SELECT '111' sid,'李一' sname FROM DUAL UNION ALL
SELECT '112' sid,'李二' sname FROM DUAL UNION ALL
SELECT '113' sid,'李三' sname FROM DUAL UNION ALL
SELECT '114' sid,'李四' sname FROM DUAL UNION ALL
SELECT '115' sid,'李五' sname FROM DUAL
)
SELECT m.sid,
c.sname,
m.job_t_sum,
m.job_o_sum,
m.adate
FROM (SELECT NVL(t1.sid, t2.sid) sid,
NVL(t1.adate, t2.adate) adate,
NVL(t1.job_t_sum, 0) job_t_sum,
NVL(t2.job_o_sum, 0) job_o_sum
FROM (SELECT a.sid,
a.adate,
SUM(a.job_t) job_t_sum,
NULL job_o_sum
FROM a
GROUP BY a.sid,
a.adate) t1
FULL OUTER JOIN (SELECT b.sid,
b.adate,
NULL job_t_sum,
SUM(b.job_o) job_o_sum
FROM b
GROUP BY b.sid,
b.adate) t2 ON t1.sid = t2.sid) m
LEFT OUTER JOIN c ON m.sid = c.sid
ORDER BY m.adate
SID SNAME JOB_T_SUM JOB_O_SUM ADATE
--- ------ ---------- ---------- --------
111 李一 12 17 20110711
113 李三 18 0 20110712
113 李三 23 0 20110713
114 李四 0 16 20110714
SELECT NVL(A.SID,B.SID) SID,NVL(A.DATE,B.DATE) DATE,A.JOB_T,B.JOB_O FROM a FULL OUTER JOIN b ON 1 = 2;
--写的有问题,重写了一下
SELECT T1.SID, C.SNAME, SUM(T1.JOB_T_SUM), SUM(T1.JOB_O_SUM), T1.ADATE
FROM (SELECT SID, ADATE, SUM(JOB_T) AS JOB_T_SUM, 0 AS JOB_O_SUM
FROM A
GROUP BY SID, ADATE
UNION
SELECT SID, ADATE, 0 AS JOB_T_SUM, SUM(JOB_O) AS JOB_O_SUM
FROM B
GROUP BY SID, ADATE) T1,
C
WHERE T1.SID = C.SID
GROUP BY T1.SID, C.SNAME, T1.ADATE
ORDER BY T1.SID;--测试结果[SYS@myorcl] SQL>WITH a AS (
2 SELECT '1' tid,'111' sid,'20110711' adate, 5 job_t FROM DUAL UNION ALL
3 SELECT '2' tid,'111' sid,'20110711' adate, 7 job_t FROM DUAL UNION ALL
4 SELECT '3' tid,'113' sid,'20110712' adate, 18 job_t FROM DUAL UNION ALL
5 SELECT '4' tid,'113' sid,'20110713' adate, 23 job_t FROM DUAL
6 ),b AS (
7 SELECT '1' tid,'111' sid,'20110711' adate, 12 job_o FROM DUAL UNION ALL
8 SELECT '2' tid,'111' sid,'20110711' adate, 5 job_o FROM DUAL UNION ALL
9 SELECT '3' tid,'114' sid,'20110714' adate, 7 job_o FROM DUAL UNION ALL
10 SELECT '4' tid,'114' sid,'20110714' adate, 9 job_o FROM DUAL
11 ),c AS (
12 SELECT '111' sid,'李一' sname FROM DUAL UNION ALL
13 SELECT '112' sid,'李二' sname FROM DUAL UNION ALL
14 SELECT '113' sid,'李三' sname FROM DUAL UNION ALL
15 SELECT '114' sid,'李四' sname FROM DUAL UNION ALL
16 SELECT '115' sid,'李五' sname FROM DUAL
17 )SELECT T1.SID, C.SNAME, SUM(T1.JOB_T_SUM), SUM(T1.JOB_O_SUM), T1.ADATE
18 FROM (SELECT SID, ADATE, SUM(JOB_T) AS JOB_T_SUM, 0 AS JOB_O_SUM
19 FROM A
20 GROUP BY SID, ADATE
21 UNION
22 SELECT SID, ADATE, 0 AS JOB_T_SUM, SUM(JOB_O) AS JOB_O_SUM
23 FROM B
24 GROUP BY SID, ADATE) T1,
25 C
26 WHERE T1.SID = C.SID
27 GROUP BY T1.SID, C.SNAME, T1.ADATE
28 ORDER BY T1.SID;SID SNAM SUM(T1.JOB_T_SUM) SUM(T1.JOB_O_SUM) ADATE
--- ---- ----------------- ----------------- --------
111 李一 12 17 20110711
113 李三 18 0 20110712
113 李三 23 0 20110713
114 李四 0 16 20110714