SELECT A,B,C FROM TABLE_A 查詢出數據如下
A B C
aa bb ak
aa bb af
aa bb mm
ab de 45
ab er bb
ab er cc
如何變成如下格式
A B C
aa bb ak
af
mm
ab de 45
er bb
cc 就是名字是上一行一樣時顯示 NULl
A B C
aa bb ak
aa bb af
aa bb mm
ab de 45
ab er bb
ab er cc
如何變成如下格式
A B C
aa bb ak
af
mm
ab de 45
er bb
cc 就是名字是上一行一樣時顯示 NULl
WHEN Q.A = LAG(A, 1, NULL) OVER(ORDER BY A) THEN
NULL
ELSE
Q.A
END A,
CASE
WHEN Q.B = LAG(B, 1, NULL) OVER(ORDER BY A) THEN
NULL
ELSE
Q.B
END B,
CASE
WHEN Q.C = LAG(C, 1, NULL) OVER(ORDER BY A) THEN
NULL
ELSE
Q.C
END C
FROM (SELECT 'aa' A, 'bb' B, 'ak' C
FROM DUAL
UNION ALL
SELECT 'aa' A, 'bb' B, 'af' C
FROM DUAL
UNION ALL
SELECT 'aa' A, 'bb' B, 'mm' C
FROM DUAL
UNION ALL
SELECT 'ab' A, 'de' B, '45' C
FROM DUAL
UNION ALL
SELECT 'ab' A, 'er' B, 'bb' C
FROM DUAL
UNION ALL
SELECT 'ab' A, 'er' B, 'cc' C FROM DUAL) Q
decode(lag(b, 1) over(partition by b order by b), b, null, b) b,
c
from table_a
order by a, b
剛剛測試了一下 上面語法可以實現
SAMPLE:下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
SELECT last_name, hire_date, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 'PU_CLERK';