我正在嘗試選擇非空白列中的最后一個值（技術上不是非空值），并為之后的每個日期選擇它，直到該值更改，然后選擇該值等等。
我有的：
| company_id | 日期 | 銷售階段 | previous_sales_stage |
|---|---|---|---|
| 1 | 2022-05-20 00:00:00.000 | 一個 | 無效的 |
| 1 | 2022-05-19 00:00:00.000 | b | 無效的 |
| 1 | 2022-05-18 00:00:00.000 | C | 無效的 |
| 1 | 2022-05-17 00:00:00.000 | C | 無效的 |
| 1 | 2022-05-16 00:00:00.000 | C | 無效的 |
| 1 | 2022-05-15 00:00:00.000 | d | 無效的 |
| 1 | 2022-05-14 00:00:00.000 | d | 無效的 |
| 1 | 2022-05-13 00:00:00.000 | d | 無效的 |
| 1 | 2022-05-12 00:00:00.000 | e | 無效的 |
| 1 | 2022-05-11 00:00:00.000 | e | 無效的 |
我想擁有的：
| company_id | 日期 | 銷售階段 | previous_sales_stage |
|---|---|---|---|
| 1 | 2022-05-20 00:00:00.000 | 一個 | b |
| 1 | 2022-05-19 00:00:00.000 | b | C |
| 1 | 2022-05-18 00:00:00.000 | C | d |
| 1 | 2022-05-17 00:00:00.000 | C | d |
| 1 | 2022-05-16 00:00:00.000 | C | d |
| 1 | 2022-05-15 00:00:00.000 | d | e |
| 1 | 2022-05-14 00:00:00.000 | d | e |
| 1 | 2022-05-13 00:00:00.000 | d | e |
| 1 | 2022-05-12 00:00:00.000 | e | 無效的 |
| 1 | 2022-05-11 00:00:00.000 | e | 無效的 |
這是一個與所有公司共享的匯總表（因此在給定日期將有多個公司 ID 和階段），并且每天從存盤的程序中計算。如果沒有價值，NULL沒關系。
下面是一些 T-SQL 來創建一個臨時表來重新創建這個例子：
DROP TABLE IF EXISTS #blah
CREATE TABLE #blah
(
company_id INT
, [date] DATETIME
, sales_stage VARCHAR(50)
, previous_sales_stage VARCHAR(50)
);
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'a',CAST(GETDATE() AS DATE))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'b',dateadd(d,-1,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'c',dateadd(d,-2,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'c',dateadd(d,-3,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'c',dateadd(d,-4,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'d',dateadd(d,-5,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'d',dateadd(d,-6,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'d',dateadd(d,-7,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'e',dateadd(d,-8,cast(getdate() as date)))
INSERT INTO #blah (company_id, sales_stage, [date]) VALUES (1,'e',dateadd(d,-9,cast(getdate() as date)))
SELECT * FROM #blah
UPDATE #blah SET previous_sales_stage = 'b' WHERE company_id = 1 AND date = '2022-05-20 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'c' WHERE company_id = 1 AND date = '2022-05-19 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'd' WHERE company_id = 1 AND date = '2022-05-18 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'd' WHERE company_id = 1 AND date = '2022-05-17 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'd' WHERE company_id = 1 AND date = '2022-05-16 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'e' WHERE company_id = 1 AND date = '2022-05-15 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'e' WHERE company_id = 1 AND date = '2022-05-14 00:00:00.000'
UPDATE #blah SET previous_sales_stage = 'e' WHERE company_id = 1 AND date = '2022-05-13 00:00:00.000'
UPDATE #blah SET previous_sales_stage = NULL WHERE company_id = 1 AND date = '2022-05-12 00:00:00.000'
UPDATE #blah SET previous_sales_stage = NULL WHERE company_id = 1 AND date = '2022-05-11 00:00:00.000'
SELECT * FROM #blah
uj5u.com熱心網友回復：
您可以使用子查詢或 OUTER APPLY，如下所示：
SELECT t1.company_id, t1.date, t1.sales_stage, x.previous_sales_stage
FROM #blah t1
OUTER APPLY (
SELECT TOP 1 t2.sales_stage AS previous_sales_stage
FROM #blah t2
WHERE t2.company_id=t1.company_id
AND t2.date<t1.date
AND t2.sales_stage<>t1.sales_stage
ORDER BY t2.date DESC
) x
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/481357.html
標籤：sql sql服务器 tsql sql-server-2019
上一篇：CASE運算式中的轉換失敗