WITH Table2 AS( SELECT 11 Table1ID, 22 Table3ID, To_Date('2011-1-01','yyyy-mm-dd') data FROM dual UNION ALL SELECT 11, 22, To_Date('2011-1-02','yyyy-mm-dd') FROM dual UNION ALL SELECT 11, 22, To_Date('2011-1-03','yyyy-mm-dd') FROM dual UNION ALL SELECT 11, 33, To_Date('2011-1-02','yyyy-mm-dd') FROM dual ), Table3 AS( SELECT 22 Table3ID, 'ff' name, 1 falg FROM dual UNION ALL SELECT 33, 'gg', 0 FROM dual )SELECT a.Table3ID,Max(b.NAME) NAME ,Max(b.falg) falg, Max(Decode(To_Char(a.DATa,'day'),'星期一',a.DATa)) 周一, Max(Decode(To_Char(a.DATa,'day'),'星期二',a.DAta)) 周二, Max(Decode(To_Char(a.DATa,'day'),'星期三',a.DATa)) 周三, Max(Decode(To_Char(a.DATa,'day'),'星期四',a.DATa)) 周四, Max(Decode(To_Char(a.DATa,'day'),'星期五',a.DATa)) 周五, Max(Decode(To_Char(a.DATa,'day'),'星期六',a.DATa)) 周六, Max(Decode(To_Char(a.DATa,'day'),'星期日',a.DAta)) 周天 FROM TABLE2 a, table3 b WHERE a.Table3ID=b.Table3ID AND a.Table1ID = '11' GROUP BY a.Table1ID,a.Table3IDTABLE3ID NAME FALG 周一 周二 周三 周四 周五 周六 周天 --------------------------------------------------------------------------------------------------- 22 ff 1 2011.01.03 2011.01.01 2011.01.02 33 gg 0 2011.01.02
SELECT 11 Table1ID, 22 Table3ID, To_Date('2011-1-01','yyyy-mm-dd') data FROM dual
UNION ALL
SELECT 11, 22, To_Date('2011-1-02','yyyy-mm-dd') FROM dual
UNION ALL
SELECT 11, 22, To_Date('2011-1-03','yyyy-mm-dd') FROM dual
UNION ALL
SELECT 11, 33, To_Date('2011-1-02','yyyy-mm-dd') FROM dual
),
Table3 AS(
SELECT 22 Table3ID, 'ff' name, 1 falg FROM dual
UNION ALL
SELECT 33, 'gg', 0 FROM dual
)SELECT a.Table3ID,Max(b.NAME) NAME ,Max(b.falg) falg,
Max(Decode(To_Char(a.DATa,'day'),'星期一',a.DATa)) 周一,
Max(Decode(To_Char(a.DATa,'day'),'星期二',a.DAta)) 周二,
Max(Decode(To_Char(a.DATa,'day'),'星期三',a.DATa)) 周三,
Max(Decode(To_Char(a.DATa,'day'),'星期四',a.DATa)) 周四,
Max(Decode(To_Char(a.DATa,'day'),'星期五',a.DATa)) 周五,
Max(Decode(To_Char(a.DATa,'day'),'星期六',a.DATa)) 周六,
Max(Decode(To_Char(a.DATa,'day'),'星期日',a.DAta)) 周天
FROM TABLE2 a, table3 b
WHERE a.Table3ID=b.Table3ID AND a.Table1ID = '11'
GROUP BY a.Table1ID,a.Table3IDTABLE3ID NAME FALG 周一 周二 周三 周四 周五 周六 周天
---------------------------------------------------------------------------------------------------
22 ff 1 2011.01.03 2011.01.01 2011.01.02
33 gg 0 2011.01.02