Sql-Server

如何將數據視為欄位值更改的歷史記錄

  • April 10, 2020
例子

我有類似的數據

id employeeId Salary Year
1 1 12000 2001
1 2 10000 2001
1 1 16000 2002
1 3 12000 2004
1 1 18000 2005
1 3 14000 2006

所以員工 ID 1 有 3 條記錄,2 有 1 條,3 有 2 條記錄
我希望輸出為

EmployeeID NewSalary 頁面
1 18000 16000
1 16000 12000
2 10000 -
3 14000 12000`

這應該適用於SQL Server 2012+

  ;with cte as
   (select employeeId,
           Salary AS 'NewSalary',
           lead(salary,1,0)over(partition by employeeid order by salary desc) as 'OldSalary',
           row_number()over(partition by employeeid order by salary desc) as rn        
    from Table_2 
    )

    select employeeId,
           NewSalary,
           OldSalary
    from cte
    where OldSalary <> 0 or (OldSalary = 0 and rn=1)

完全修改後的答案 - 請參閱我的舊答案的小提琴here。使用 OP 給出的數據,我的回答和@osiris_v6 的answer工作都是如此。

但是,當我添加額外數據時,我們的兩個答案都以同樣的方式失敗(請參閱mine@osiris_v6's)。以這種方式讓 SQL 的結果取決於記錄數並不是一個好主意。

我為解決這個問題所做的事情如下

創建和填充表

CREATE TABLE emp
(
 id INTEGER NOT NULL,
 employee_id INTEGER NOT NULL,
 salary INTEGER NOT NULL,
 s_year INTEGER NOT NULL
);

填充它

INSERT INTO emp 
VALUES
(1   ,   1       ,    18000  ,   2005),
(1   ,   1       ,    16000  ,   2002),
(1   ,   1       ,    12000  ,   2001),
(1   ,   1       ,    10000  ,   2000), -- extra record
(1   ,   1       ,     8000  ,   1999), -- extra record

(1   ,   2       ,    10000  ,   2001),

(1   ,   3       ,    14000  ,   2006),
(1   ,   3       ,    12000  ,   2004);

然後執行這個查詢

WITH cte AS
(
 SELECT 
   employee_id,
   salary AS new_sal,
   LEAD(salary, 1, 0) OVER (PARTITION BY employee_id 
                                ORDER BY salary DESC) AS old_sal,
   ROW_NUMBER()       OVER (PARTITION BY employee_id 
                                ORDER BY salary DESC) AS rn        
   FROM emp 
)
SELECT 
 employee_id,
 new_sal,
 old_sal,
 rn
FROM cte
WHERE rn = 1 OR (rn = 2 AND old_sal != 0);  -- big change in this line!

結果

employee_id     new_sal     old_sal     rn
         1       18000       16000     1
         1       16000       12000     2
         2       10000           0     1
         3       14000       12000     1

Et voilà - 所需的結果(見 fiddle here),但它適用於任何數據集。顯然,我的這個新答案很大程度上歸功於@osiris_v6,我感謝他們從中學到的教訓,特別是它的優雅!

ps 你可能會考慮為你的表和數據使用小提琴來解決未來的任何問題?此外,還請始終提供您的 SQL Server 版本,它可以對答案產生真正的影響(我的小提琴是 SQL Server 2014,但它也應該適用於 2012)。

我的個人資料上有一些關於如何在此處提問的文章-您可能想看一下?視窗功能非常強大,會回報您學習它們的許多倍努力!

歡迎來到論壇!

引用自:https://dba.stackexchange.com/questions/264672