我在一個表中有以下結果，我需要得到的是頻率列到適當的行上，因此頻率列中的 NULL 值被替換為適當的值。注意 CustomerCode 不同的值。
任何人都知道如何做到這一點，并在這樣做時洗掉沒有型別和型別數量的頻率行？
CustomerCode Frequency Type TypeAmount
C12345 Monthly NULL NULL
C12345 NULL A1 5.00
C12345 NULL A2 20.00
C12345 Fortnightly NULL NULL
C12345 NULL A1 5.00
C12345 NULL A2 20.00
C56789 Fortnightly NULL NULL
C56789 NULL A1 50.00
期望輸出
CustomerCode Frequency Type TypeAmount
C12345 Monthly A1 5.00
C12345 Monthly A2 20.00
C12345 Fortnightly A1 5.00
C12345 Fortnightly A2 20.00
C56789 Fortnightly A1 50.00
樣本資料
Create Table #Data
(
CustomerCode varchar(50),
Frequency varchar(50) NULL,
Type varchar(50) NULL,
TypeAmount money NULL
)
insert into #Data
(
CustomerCode,
Frequency,
Type,
TypeAmount
)
select
'C12345',
'Monthly',
NULL,
NULL
union all
select
'C12345',
NULL,
'A1',
'5.00'
union all
select
'C12345',
NULL,
'A2',
'20.00'
union all
select
'C12345',
'Fornightly',
NULL,
NULL
union all
select
'C12345',
NULL,
'A1',
'5.00'
union all
select
'C12345',
NULL,
'A2',
'20.00'
union all
select
'C56789',
'Fornightly',
NULL,
NULL
union all
select
'C56789',
NULL,
'A1',
'50.00'
select * from #Data
uj5u.com熱心網友回復：
RECURSIVE CTE 應該做的伎倆：
With cte AS
(
SELECT customerCode, frequency, type, TypeAmount, rn
FROM
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY CustomerCode ORDER BY CustomerCode) AS rn
FROM #data
) AS d
WHERE Frequency IS NOT NULL
UNION ALL
SELECT d2.customerCode, cte.frequency, d2.type, d2.TypeAmount, d2.rn
From
(
SELECT *, ROW_NUMBER()OVER(PARTITION BY CustomerCode ORDER BY CustomerCode) AS rn
FROM #data
) AS d2
INNER JOIN cte
ON d2.rn=cte.rn 1
AND d2.CustomerCode=cte.CustomerCode
WHERE d2.Frequency IS NULL
)
SELECT *
FROM cte
WHERE Type IS NOT NULL
AND TypeAmount IS NOT NULL
ORDER BY CustomerCode, rn;
結果：
| 客戶代碼 | 頻率 | 型別 | 型別金額 | 恩 |
|---|---|---|---|---|
| C12345 | 每月 | A1 | 5.0000 | 2 |
| C12345 | 每月 | A2 | 20.0000 | 3 |
| C12345 | 每晚 | A1 | 5.0000 | 5 |
| C12345 | 每晚 | A2 | 20.0000 | 6 |
| C56789 | 每晚 | A1 | 50.0000 | 2 |
查詢說明：
- 創建一個
rownumber以便我們可以參考上一行來獲取frequency - 第一部分
CTE將獲取NON NULL頻率，第二部分將獲取NULL頻率 - 以 1 行的方式加入兩者以獲得各自的
Frequency
在此處查看演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/399917.html
標籤：sql sql-server 查询语句 sql-server-2017
上一篇：從表oracle中洗掉所有約束