我想知道如何為每筆付款獲取前 6 個月的PaymentProfile，下面顯示了我正在尋找的示例，但不確定它在 SQL 語法中的外觀：
P = Paid
U = Unpaid
? = Pending
From this output:
| PaidHistory | PersonId | PaymentId | PaymentDueDt |
..... .... .... .....
| P | 101 | 23 | 2022-01-26 |
| U | 101 | 24 | 2022-02-26 |
| P | 101 | 25 | 2022-03-26 |
| P | 101 | 26 | 2022-04-26 |
| P | 101 | 27 | 2022-05-26 |
| P | 101 | 28 | 2022-06-26 |
| U | 101 | 29 | 2022-07-26 |
| P | 101 | 30 | 2022-08-26 |
| ? | 101 | 31 | 2022-09-26 |
To this output:
| PaidHistory | PersonId | PaymentId | PaymentDueDt | PaymentProfile |
..... .... .... ..... ..........
| P | 101 | 23 | 2022-01-26 | PPPPPP |
| U | 101 | 24 | 2022-02-26 | PPPPPU |
| P | 101 | 25 | 2022-03-26 | PPPPUP |
| P | 101 | 26 | 2022-04-26 | PPPUPP |
| P | 101 | 27 | 2022-05-26 | PPUPPP |
| P | 101 | 28 | 2022-06-26 | PUPPPP |
| U | 101 | 29 | 2022-07-26 | UPPPPU |
| P | 101 | 30 | 2022-08-26 | PPPPUP |
| ? | 101 | 31 | 2022-09-26 | PPPUP? |
那么基本上如何創建諸如 PaymentProfile 之類的列？顯示該特定 PaymentDueDt 的 6 個月付款。
uj5u.com熱心網友回復：
你在這里有一個重疊的組問題，你可以通過執行自連接來解決它。如果您的 SQL Server 版本支持該STRING_AGG功能，您可以嘗試以下操作：
SELECT T.PaidHistory, T.PersonId, T.PaymentId, T.PaymentDueDt,
STRING_AGG(D.PaidHistory, '') WITHIN GROUP (ORDER BY D.PaymentDueDt) PaymentProfile
FROM table_name T JOIN table_name D
ON D.PaymentDueDt BETWEEN DATEADD(MONTH, -5, T.PaymentDueDt) AND T.PaymentDueDt
AND T.PersonId = D.PersonId
GROUP BY T.PaidHistory, T.PersonId, T.PaymentId, T.PaymentDueDt
ORDER BY T.PersonId, T.PaymentDueDt
看一個演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/533821.html
標籤：数据库sql服务器