需要在某些列上具有唯一值的行但日期列沒有幫助？

我有一個表，每個員工都有多條記錄。我需要按員工查找任何列值發生變化的所有記錄。例如，我有喬治的以下數據：

CREATE TABLE tableName
(
    NAME   varchar(300),
    HOME   varchar(300),
    CURRENT    varchar(300),
    EFFECTIVE_DATE     varchar(300),
    NOT_TO_EXCEED_DATE     varchar(300),
    FLAG   varchar(300),
    GRADE  varchar(300),
    POSITION   varchar(300),
    LAST_PAY_PERIOD_PAID   varchar(255)
);

INSERT INTO tableName ( NAME , HOME , CURRENT , EFFECTIVE_DATE , NOT_TO_EXCEED_DATE , FLAG , GRADE , POSITION , LAST_PAY_PERIOD_PAID )
VALUES
   ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR  ACCOUNTANT', '202107'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR  ACCOUNTANT', '202105'),
   ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR  ACCOUNTANT', '202105'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR  ACCOUNTANT', '202103'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202026'),
   ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202023'),
   ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202024'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202025'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202025'),
   ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202023'),
   ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202024'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR  ACCOUNTANT', '202104'),
   ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR  ACCOUNTANT', '202106'),
   ('GEORGE', '610124', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202025'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202101'),
   ('GEORGE', '610422', '610422', 'NULL', 'NULL', 'N', '15', 'SUPERVISOR  ACCOUNTANT', '202108'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202102'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '14', ' ACCOUNTANT', '202102'),
   ('GEORGE', '610422', '630100', '11/10/2019', '11/6/2021', 'Y', '15', 'SUPERVISOR  ACCOUNTANT', '202103');

如何獲得以下結果數據集？

NAME,HOME,CURRENT,EFFECTIVEDATE,NOTTOEXCEEDDATE,FLAG,GRADE,POSITION,LASTPAYPERIODPAID
GEORGE,610124,630100,11/10/2019,11/6/2021,Y,14, ACCOUNTANT,202023
GEORGE,610422,630100,11/10/2019,11/6/2021,Y,15,SUPERVISOR  ACCOUNTANT,202103
GEORGE,610422,610422,NULL,NULL,N,15,SUPERVISOR  ACCOUNTANT,202105

到目前為止，我只能通過不帶LAST_PAY_PERIOD_PAID列的 Select Distinct 來獲取結果數據集。鑑於我確實需要知道記錄何時根據支付期發生更改，所以一旦添加此列，我就會再次獲得第一個表。

當兩條相同的記錄有不同的值時，最新的LAST_PAY_PERIOD就可以了。

也許我誤解了目標，但是，為了獲取您在 中提供的數據INSERT並確定記錄更改的時間段和最近支付的時間段，您可以這樣做：

SELECT 
 tn.[name], 
 tn.[home], 
 tn.[current], 
 tn.[effective_date], 
 tn.[not_to_exceed_date], 
 tn.[flag], 
 tn.[grade], 
 TRIM(tn.[position]) as [position],
 MIN(tn.[last_pay_period_paid]) as [first_pay_period_paid], 
 MAX(tn.[last_pay_period_paid]) as [recent_pay_period_paid]
FROM 
 [dbo].[tableName] tn
GROUP BY 
 tn.[name], 
 tn.[home],
 tn.[current],
 tn.[effective_date],
 tn.[not_to_exceed_date],
 tn.[flag],
 tn.[grade],
 TRIM(tn.[position])

這將為您提供以下輸出：

請注意， 有兩條記錄ACCOUNTANT。這是因為 中有兩個不同的值home。不確定這是否是由於範例數據中的拼寫錯誤，或者該列是否是必要的。無論哪種方式……這可能會給你你所尋求的資訊。

引用自：https://dba.stackexchange.com/questions/290720