>我有三个表格,如下图所示。我想做的是,
- 列出上次恢复日期为 30 天的缔约方。
- 列出上次恢复日期为 60 天的缔约方。
- 列出上次恢复日期为 90 天的缔约方。
我使用的是在XAMPP和SQL Server 2008 R2上运行的PHP 7.4,但我想通过纯粹的SQL方式实现它。
这是表架构(粗略(
dbo.Parties
PartyID
PartyName
dbo.Recovery
RecoveryID
RecoveryDate
dbo.RecoveryBody
RecoveryID
CustomerID //PartyID
AmountRecoverd
我想要这样的东西(以及 30、60、90 天的间隔(。我没有得到想要的结果,因为它给了我多个PartyName实例,因为我只想要一个具有上次恢复日期和恢复金额的PartyName实例。
SELECT Parties.PartyName, CAST(RecoveryDate AS date) AS RecoveryDate, AmountRecoverd
FROM RecoveryBody
LEFT JOIN Recovery ON RecoveryBody.RecoveryID = Recovery.RecoveryID
LEFT JOIN Parties ON Parties.PartyID = RecoveryBody.CustomerID
GROUP BY PartyName, RecoveryDate, AmountRecoverd
ORDER BY RecoveryDate DESC
你可以尝试下面这样的东西。不确定结果,因为我没有数据要测试。但是您可以使用CTE获得所需的结果
WITH CTE AS(
SELECT distinct Parties.PartyID,
Parties.PartyName,
CAST(RecoveryDate AS date) AS RecoveryDate,
AmountRecoverd,
ROW_NUMBER()over (partition by Parties.PartyID ORDER BY RecoveryDate DESC) RN
FROM RecoveryBody
LEFT JOIN Recovery ON RecoveryBody.RecoveryID = Recovery.RecoveryID
LEFT JOIN Parties ON Parties.PartyID = RecoveryBody.CustomerID)
SELECT *
FROM CTE
WHERE RN=1