如何选择不同的行，但最大日期？
如何选择不同的行，但最大日期？
提问于 2019-10-17 19:26:01
假设我有一张表：
Table A
Period Name| Type Name| Date | Quantity
-----------------------------
Yearly | General | 2019-09-01 | 24
Yearly | General | 2019-11-01 | 30
Yearly | Casual | 2019-09-01 | 6我想让所有的行都按PeriodName，TypeName，但最大的日期分组。
我有group by max date，然后与原始表连接以获得结果：
SELECT dbo.tbl_WPT_LeaveType.TypeName, dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeaveType_ID, dbo.tbl_WPT_LeavePeriod.PeriodName,
dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeavePeriod_ID, dbo.tbl_WPT_EmployeeLeavePolicy.Leave, dbo.tbl_WPT_EmployeeLeavePolicy.WithOutRequest,
dbo.tbl_WPT_EmployeeLeavePolicy.ApplyFrom
FROM dbo.tbl_WPT_EmployeeLeavePolicy INNER JOIN
dbo.tbl_WPT_LeaveType ON dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeaveType_ID = dbo.tbl_WPT_LeaveType.ID INNER JOIN
dbo.tbl_WPT_LeavePeriod ON dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeavePeriod_ID = dbo.tbl_WPT_LeavePeriod.ID INNER JOIN
---------same table to get max date
(SELECT TOP (100) PERCENT MAX(tbl_WPT_EmployeeLeavePolicy_1.ApplyFrom) AS ApplyFrom, tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeaveType_ID,
tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeavePeriod_ID
FROM dbo.tbl_WPT_EmployeeLeavePolicy AS tbl_WPT_EmployeeLeavePolicy_1 INNER JOIN
dbo.tbl_WPT_LeaveType AS tbl_WPT_LeaveType_1 ON tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeaveType_ID = tbl_WPT_LeaveType_1.ID INNER JOIN
dbo.tbl_WPT_LeavePeriod AS tbl_WPT_LeavePeriod_1 ON tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeavePeriod_ID = tbl_WPT_LeavePeriod_1.ID
WHERE (tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_Employee_ID = @EmpID) AND (tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeavePeriod_ID = 2) AND
(tbl_WPT_EmployeeLeavePolicy_1.ApplyFrom <= @MonthFrom)
GROUP BY tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeaveType_ID, tbl_WPT_EmployeeLeavePolicy_1.FK_tbl_WPT_LeavePeriod_ID) AS x ON x.FK_tbl_WPT_LeaveType_ID = dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeaveType_ID AND x.FK_tbl_WPT_LeavePeriod_ID = dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeavePeriod_ID AND x.ApplyFrom = dbo.tbl_WPT_EmployeeLeavePolicy.ApplyFrom
WHERE (dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_Employee_ID = @EmpID) AND (dbo.tbl_WPT_EmployeeLeavePolicy.FK_tbl_WPT_LeavePeriod_ID = 2) AND
(dbo.tbl_WPT_EmployeeLeavePolicy.ApplyFrom <= @MonthFrom)Period Name| Type Name| Date | Quantity
-----------------------------------------------
Yearly | General | 2019-11-01 | 30
Yearly | Casual | 2019-09-01 | 6回答 1
Stack Overflow用户
发布于 2019-10-17 21:20:28
我建议使用公用表表达式和row_number()函数来标识具有最大日期的记录。
例如：
create table #test (
[Period Name] varchar(20),
[Type Name] varchar(20),
[Date] date,
Quantity int
)
insert #test values ('Yearly', 'General', '2019-09-01', 24)
, ('Yearly', 'General', '2019-11-01', 30)
, ('Yearly', 'Casual', '2019-09-01', 6)
;with c as (select [Period Name]
, [Type Name]
, [Date]
, Quantity
, row_number() over (partition by [Period Name], [Type Name] order by [date] desc) rn
from #test
)
select *
from c
where rn = 1结果如下：
Period Name Type Name Date Quantity rn
Yearly Casual 2019-09-01 6 1
Yearly General 2019-11-01 30 1 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58431531
复制相关文章
相似问题
腾讯云开发者
