A1 A2 A3 A4 A521 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直接写该怎么写?
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直接写该怎么写?
select '张三','语文',78 from dual union all
select '张三','数学',87 from dual union all
select '张三','英语',82 from dual union all
select '张三','物理',90 from dual union all
select '李四','语文',65 from dual union all
select '李四','数学',77 from dual union all
select '李四','英语',65 from dual union all
select '李四','物理',85 from dual;select
Student,
max(case Course when '数学' then Score else 0 end) as 数学,
max(case Course when '物理' then Score else 0 end) as 物理,
max(case Course when '英语' then Score else 0 end) as 英语,
max(case Course when '语文' then Score else 0 end) as 语文
from
Class
group by Student;
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.GB1
UNION ALL
SELECT 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
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.GB1
UNION ALL
SELECT 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.GB1
UNION ALL
SELECT 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.GB1
UNION ALL
SELECT 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.GB1
UNION ALL
SELECT 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
这样查出的结果你看一下
然后再在程序中拆解
在程序里拆解很简单就不给你写了