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

admin

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是这2个表之间共享的唯一列，那么如何告诉SQL忽略SchID读取并仅显示第二行的显示？

阅读 181

2021-06-07

共1个答案

admin

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

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

2021-06-07