时间戳

首页 > 解决方案 > SQL - 如何在特定列中选择具有最大值的数据行？

sql - SQL - 如何在特定列中选择具有最大值的数据行？

问题描述

在测试 SQL 查询以准备创建视图的过程中，我第一次想出了这个公式

select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo]
from [SQL3].[dbo].[Training_Record] a inner join 

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.SchID, z.CourseID)as b

    on a.SchID = b.SchID 

-- to test data content
where EmpNo = '141281' and CourseID = '22'

结果给了我2行：

| SchID | CourseID | EmpNo |       ActStartDate      |         ValidTo         |
--------------------------------------------------------------------------------
| 5000  |    22    | 14000 | 2018-06-11 00:00:00.000 | 2018-12-10 00:00:00.000 |
| 5022  |    22    | 14000 | 2018-08-08 00:00:00.000 | 2019-02-07 00:00:00.000 |

我想要第二行，它的 ActStartDate 是唯一出现的最大的。

| SchID | CourseID | EmpNo |       ActStartDate      |         ValidTo         |
--------------------------------------------------------------------------------
| 5022  |    22    | 14000 | 2018-08-08 00:00:00.000 | 2019-02-07 00:00:00.000 |

但是由于 SchID 具有不同的编号，它最终会注册为单独的条目。SchID 是这两个表之间共享的唯一列，那么我如何告诉 SQL 忽略 SchID 读取并只显示第二行？

标签： sqlsql-servertsql

解决方案

您可以在下面尝试 - 使用子查询

select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo]
from [SQL3].[dbo].[Training_Record] a 
inner join

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.CourseID)as b
on a.SchID = b.SchID 

where EmpNo = '141281' and CourseID = '22' and 
b.ActStartDate in (select max([ActStartDate]) from [SQL3].[dbo].[Training_Schedule] z1 where b.courseid=z1.courseid group by z1.courseid)

或者您可以尝试使用row_number()

select * from 
(
select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo],row_number() over(partition by b.courseid order by b.ActStartDate desc) as rn
from [SQL3].[dbo].[Training_Record] a inner join 

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.SchID, z.CourseID)as b

    on a.SchID = b.SchID 

-- to test data content
where EmpNo = '141281' and CourseID = '22'
)A where rn=1

推荐阅读