拉出最小日期

我正在嘗試將兩個表連接在一起，它們僅共享一個“帳號”作為它們之間的連接欄位。一個表（表 1）是靜態的，每個帳號僅包含一條記錄，但另一個連接表（表 2）包含每個帳號的多個日期記錄。使用臨時表，我試圖首先從表 2 中提取不同的日期記錄，然後通過帳號將它們加入表 1。

SELECT DISTINCT
   CAST(tlpsf.ACCOUNT_NUMBER AS NCHAR(16)) AS psAccountNumber
   ,tlpsf.APPL_ID AS psApplID
   --,MIN(CONVERT(VARCHAR(10),tlpsf.DATE_PYMT_DUE_FIRST,120)) AS psDatePmtDueFirst
   ,MIN(CAST(tlpsf.DATE_PYMT_DUE_FIRST AS datetime2)) AS psDatePmtDueFirst
   ,tlpsf.NBR_OF_PMTS_THIS_SCHEDULE AS psNbrPmtsThisSchedule
   , CAST(tlpsf.PAYMENT_SCHEDULE_AMOUNT AS NUMERIC(17,2)) AS psPmtScheduleAmt
   , tlpsf.PAYMENT_SCHEDULE_TYPE AS psPmtScheduleType
   , tlpsf.ROW_PROCESSED_THRU AS psRowProcessedThru

INTO #loanpmtschedules
FROM dbo.T_LOAN_PYMT_SCHEDULE_FILE_ME tlpsf
WHERE tlpsf.ROW_PROCESSED_THRU = @strLastofMonth
GROUP BY ACCOUNT_NUMBER, APPL_ID, NBR_OF_PMTS_THIS_SCHEDULE, PAYMENT_SCHEDULE_AMOUNT, PAYMENT_SCHEDULE_TYPE, ROW_PROCESSED_THRU

但是，由於某種原因，我的 Temp 表建構仍在每個帳號中提取多個日期記錄。

這是一個使用ROW_NUMBERTable2為grouped byAccountNumber和 order by中的每一行分配行號的範例PaymentDate。然後，我們可以加入Table1行號為 1 的位置，Table2這意味著每個帳戶的最短付款日期。AccountNumber``Table2

--demo setup
Declare @Table1 table (AccountNumber int)
Declare @Table2 table (AccountNumber int, PaymentDate date, OtherStuff varchar(50))

insert into @Table1 (AccountNumber) values(1)
insert into @Table1 (AccountNumber) values(2)
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(1,'2019-01-01','jan')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(1,'2019-02-01','feb')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(1,'2019-02-01','mar')

insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(2,'2019-05-01','may')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(2,'2019-06-01','jun')
insert into @Table2 (AccountNumber, PaymentDate, OtherStuff) values(2,'2019-07-01','jul')

--solution
SELECT t1.AccountNumber
   ,t2.PaymentDate
   ,OtherStuff
FROM @Table1 t1
JOIN (
   SELECT *
       ,ROW_NUMBER() OVER (
           PARTITION BY AccountNumber ORDER BY PaymentDate
           ) AS rn
   FROM @Table2
   ) t2
   ON t2.AccountNumber = t1.AccountNumber
WHERE t2.rn = 1

---

| AccountNumber | PaymentDate | OtherStuff |
|---------------|-------------|------------|
| 1             | 2019-01-01  | jan        |
| 2             | 2019-05-01  | may        |

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