A1 A2 A3 A4 A5
21 1 2 3 4
22 5 6 7 8
23 9 10 11 12
24 13 14 15 16
有这样一个表，
想得到
21 22 23 24
1 5 9 13
2 6 10 14
3 7 11 15
4 8 12 16
这样的数据。不用游标，用sql直接写该怎么写？
------解决方案--------------------
晕我想不出别的方法来，先贴我的，比如你的表名为A
- SQL code
SELECT SUM(DECODE(T1.A1, 21, T1.A1, 0)) A1, SUM(DECODE(T1.A1, 22, T1.A1, 0)) A2, SUM(DECODE(T1.A1, 23, T1.A1, 0)) A3, SUM(DECODE(T1.A1, 24, T1.A1, 0)) A4 FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT T1.A1, T2.A2, T3.A3, T4.A4 FROM (SELECT T.A1, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 1, A.A2, 0)) A1 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 2, A.A3, 0)) A1 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 3, A.A4, 0)) A1 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 4, A.A5, 0)) A1 FROM A) T) T1, (SELECT T.A2, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 5, A.A2, 0)) A2 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 6, A.A3, 0)) A2 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 7, A.A4, 0)) A2 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 8, A.A5, 0)) A2 FROM A) T) T2, (SELECT T.A3, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 9, A.A2, 0)) A3 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 10, A.A3, 0)) A3 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 11, A.A4, 0)) A3 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 12, A.A5, 0)) A3 FROM A) T) T3, (SELECT T.A4, ROWNUM RN FROM (SELECT SUM(DECODE(A.A2, 13, A.A2, 0)) A4 FROM A UNION ALL SELECT SUM(DECODE(A.A3, 14, A.A3, 0)) A4 FROM A UNION ALL SELECT SUM(DECODE(A.A4, 15, A.A4, 0)) A4 FROM A UNION ALL SELECT SUM(DECODE(A.A5, 16, A.A5, 0)) A4 FROM A) T) T4 WHERE T1.RN = T2.RN AND T2.RN = T3.RN AND T3.RN = T4.RN
------解决方案--------------------
提供一个思路你看看
oracle10g
比如说你的表是A
- SQL code
SELECT WMSYS.WM_CONCAT(T1.A1) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A2) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A3) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A4) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1UNION ALLSELECT WMSYS.WM_CONCAT(T1.A5) RESULT FROM (SELECT T.*, SUM(T.GB) OVER(ORDER BY T.A1) GB1 FROM (SELECT A.*, DECODE(A.A1 - LAG(A.A1, 1, -1) OVER(ORDER BY A1), 1, 0, 1) GB FROM A) T) T1 GROUP BY T1.GB1