代码之家 › 专栏 › 技术社区 › Andrew

SQL:选择每组返回多条记录的前1个组

sql-server sql

Andrew · 技术社区 · 6 年前

| Id | InvestorFundId | Name | AccountKey | AsOfDate | AddedOn  |
| 1  | 11111          | Name1| Key1       | 9/5/2018 | 8/5/2018 |
| 2  | 11111          | Name2| Key1       | 9/3/2018 | 8/5/2018 |
| 3  | 22222          | Name3| Key2       | 9/2/2018 | 8/5/2018 |
| 4  | 33333          | Name4| Key3       | 9/2/2018 | 8/5/2018 |
| 5  | 33333          | Name5| Key3       | 9/4/2018 | 8/5/2018 |

InvestorFundId AccountKey AsOfDate 和 AddedOn 下降。

预期结果如下:

| InvestorFundId | Name | AccountKey |
| 11111          | Name1| Key1       | 
| 22222          | Name3| Key2       |
| 33333          | Name5| Key3       |

我已经查看了一些帖子,但我无法正确返回行,以下是我目前的情况:

SELECT Name, AccountKey, H.InvestorFundId FROM
[Investor.Fund.History] H
CROSS APPLY(SELECT TOP 1 InvestorFundId 
FROM [Investor.Fund.History] 
WHERE  DataStatusId = 1 AND AsOfYearMonth <= 201806
ORDER BY AsOfDate DESC, AddedOn DESC) HS
ORDER BY H.InvestorFundId

谢谢你

注意:我知道我的where中有两个额外的列,我只是选择从图表中排除这些列,但是你应该能够得到要点

2 回复 | 直到 6 年前

Pedro Martins 6 年前

您可以使用CTE获取所需的数据。

USE tempdb;
GO

DECLARE @table TABLE (Id INT IDENTITY(1, 1), InvestorFundId INT, Name VARCHAR(50), AccountKey VARCHAR(50), AsOfDate DATE, AddedOn DATE);
INSERT INTO @table VALUES (11111, 'Name1', 'Key1', '9/5/2018', '8/5/2018');
INSERT INTO @table VALUES (11111, 'Name2', 'Key1', '9/3/2018', '8/5/2018');
INSERT INTO @table VALUES (22222, 'Name3', 'Key2', '9/2/2018', '8/5/2018');
INSERT INTO @table VALUES (33333, 'Name4', 'Key3', '9/2/2018', '8/5/2018');
INSERT INTO @table VALUES (33333, 'Name5', 'Key3', '9/4/2018', '8/5/2018');

;WITH CTE AS
(
    SELECT InvestorFundId, Name, AccountKey, ROW_NUMBER() OVER (PARTITION BY InvestorFundID ORDER BY AsOfDate DESC) AS RowId FROM @table
)
SELECT InvestorFundId, Name, AccountKey
    FROM CTE
    WHERE RowId = 1;

SQLFiddle

S3S 6 年前

你可以用 WITH TIES 简单地申请 ROW_NUMBER ORDER BY

Select top 1 with ties *
From History
Where DataStatusId = 1 and AsOfYearMonth = 201806
Order by 
Row_Number() over (partition by InvestorFundID order by AsOfDate desc)

或使用子查询

Select  *
    From (select * , RN= Row_Number() over (partition by InvestorFundID order by AsOfDate desc)
             From History
             Where DataStatusId = 1 and AsOfYearMonth = 201806) x
Where x.RN = 1

如果你觉得这慢,那么我们需要看看执行计划,以确定为什么它慢。InvestorFundId上的一个非聚集索引AsOfDate desc将使这个过程变得非常快。

Create nonclustered index indexName on
History (InvestorFundID, AsOfDate desc)