如何在连接查询中应用max函数?

Question

我是SQL Server的新手，我有3个表考虑一个员工表

<表类> empid 名称 位置 tbody><<tr>1abc美国2efg英国

请尝试一下

select e.empid, e.location, h.holidaycode, [date], max([type])
from employee e inner join location_table l 
on e.location=l.location
inner join holidaytable h 
on l.holidaycode=h.holidaycode
where e.empid=2 and h.[date]='2022-01-01'
group by e.empid, e.location, h.holidaycode, [date]

的例子:

Schema和insert语句:

create table employee(empid int, name varchar(50), location varchar(50));
insert into employee values(1,   'abc',  'USA');
insert into employee values(2,   'efg',  'UK');

create table location_table(location varchar(50),holidaycode varchar(50));
insert into location_table values('uk','uk1');
insert into location_table values('usa','usa1');

create table holidaytable(holidaycode    varchar(50), [date] date, [type] varchar(50));
insert into holidaytable values('uk1',   '2022-01-01',   'LM');
insert into holidaytable values('uk1',   '2022-01-01',   'RMC');

查询:

select e.empid, e.location, h.holidaycode, [date], max([type]) [type 1]
from employee e inner join location_table l 
on e.location=l.location
inner join holidaytable h 
on l.holidaycode=h.holidaycode
where e.empid=2 and h.[date]='2022-01-01'
group by e.empid, e.location, h.holidaycode, [date]

输出:

您可以使用ROW_NUMBER只获取每个分组的第一行。

使用ORDER BY type DESC或ORDER BY CASE WHEN type = 'RMC' THEN 1 ELSE 2 END

SELECT
e.empid,
e.location,
l.holidaycode,
h.date,
h.type
FROM employee e
JOIN location l ON l.location = e.location
JOIN (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY h.holidaycode, h.date ORDER BY h.type DESC)
FROM holiday h
) h ON h.holidaycode = l.holidaycode AND h.rn = 1
WHERE h.date = '2022-01-01';

,db&lt的在小提琴