我在我的 oracle 11g XE 資料庫中的表格下方。
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
Legacy ID 1234
9/1/2022 25035389 MT REC DEU BGD 123.76
0.558
9/1/2022 25035390 MT REC DEU BGD 123.76
0.558
9/1/2022 25035391 MT REC DEU BGD 123.76
0.558
我想要的輸出應該如下表所示：
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
Legacy ID 1234
9/1/2022 1234 25035389 MT REC DEU BGD 123.76
9/1/2022 1234 25035389 0.558
9/1/2022 1234 25035390 MT REC DEU BGD 123.76
9/1/2022 1234 25035390 0.558
9/1/2022 1234 25035391 MT REC DEU BGD 123.76
9/1/2022 1234 25035391 0.558
如何通過 sql 獲得所需的輸出
uj5u.com熱心網友回復：
使用您的樣本資料：
WITH
tbl AS
(
Select 'Legacy ID' "COL1", '1234' "COL2", Null "COL3", Null "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual Union All
Select '9/1/2022' "COL1", Null "COL2", 25035389 "COL3", 'MT' "COL4", 'REC' "COL5", 'DEU' "COL6", 'BGD' "COL7", '123.76' "COL8" From Dual Union All
Select Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual Union All
Select '9/1/2022' "COL1", Null "COL2", 25035390 "COL3", 'MT' "COL4", 'REC' "COL5", 'DEU' "COL6", 'BGD' "COL7", '123.76' "COL8" From Dual Union All
Select Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual Union All
Select '9/1/2022' "COL1", Null "COL2", 25035391 "COL3", 'MT' "COL4", 'REC' "COL5", 'DEU' "COL6", 'BGD' "COL7", '123.76' "COL8" From Dual Union All
Select Null "COL1", Null "COL2", Null "COL3", '0.558' "COL4", Null "COL5", Null "COL6", Null "COL7", Null "COL8" From Dual
)
-- Sample data
-- COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
-- --------- ---- ---------- ----- ---- ---- ---- ------
-- Legacy ID 1234
-- 9/1/2022 25035389 MT REC DEU BGD 123.76
-- 0.558
-- 9/1/2022 25035390 MT REC DEU BGD 123.76
-- 0.558
-- 9/1/2022 25035391 MT REC DEU BGD 123.76
-- 0.558
試試這樣：
SELECT
CASE WHEN COL1 Is NULL THEN Min(COL1) OVER(Partition By 1 Order By 1 Rows Between UNBOUNDED PRECEDING AND CURRENT ROW) ELSE COL1 END "COL1",
CASE WHEN COL2 Is NULL THEN First_Value(COL2) OVER(Partition By 1 Order By 1 Rows Between UNBOUNDED PRECEDING AND CURRENT ROW) ELSE COL2 END "COL2",
CASE WHEN COL3 Is NULL THEN Max(COL3) OVER(Partition By 1 Order By 1 Rows Between 1 PRECEDING And CURRENT ROW) ELSE COL3 END "COL3",
CASE WHEN COL4 = 'MT' THEN COL4 END "COL4",
CASE WHEN COL4 = 'MT' THEN COL5 ELSE COL4 END "COL5",
COL6 "COL6",
COL7 "COL7",
COL8 "COL8"
FROM
tbl
...
-- R e s u l t :
-- COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
-- ------- ---- ---------- ----- ----- ---- ---- ------
-- Legacy ID 1234
-- 9/1/2022 1234 25035389 MT REC DEU BGD 123.76
-- 9/1/2022 1234 25035389 0.558
-- 9/1/2022 1234 25035390 MT REC DEU BGD 123.76
-- 9/1/2022 1234 25035390 0.558
-- 9/1/2022 1234 25035391 MT REC DEU BGD 123.76
-- 9/1/2022 1234 25035391 0.558
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/506876.html
標籤：sql 甲骨文 oracle11gr2
上一篇：我的作業有問題-加入顯示沒有資料