具有聚合函数的 SQL 查询，位于一对多关系的左侧连接处

Question

我有一个一对多的关系，每个用户都有许多任务，这些任务按难度进行评级。我想查询一个列表，显示每个用户一次以及他们最困难的任务。

用户表：user_id，用户名

任务表：user_id、task_id、任务名称、难度

我尝试过这样的查询

SELECT u.user_id, u.username, t.task_id, t.taskname, MAX(t.difficulty)
FROM users u
LEFT JOIN tasks t ON u.user_id = t.user_id
GROUP BY u.user_id

但是，我在运行此功能时收到not in GROUP BY clause错误。

Answer 1

假设一个用户没有两个最大难度相同的任务，你可以做这样的事情。 虽然，这不是很高性能。 它可以在小数据集上正常工作，但如果数据集非常大，则应重新设计。 希望它能让你指向正确的方向。

declare @users table (user_id int, username varchar(10))
declare @tasks table (task_id int, user_id int, taskname varchar(24), difficulty int)
insert into @users values
(1, 'John'),
(2, 'Sally'),
(3, 'Sam')
insert into @tasks values
(1, 1, 'prepare grocery list', 1),
(2, 1, 'do shopping', 2),
(3, 1, 'cook food', 3),
(4, 2, 'do shopping', 2),
(5, 2, 'prepare grocery list', 1),
(6, 3, 'cook food', 3)
select u.user_id, u.username, t.task_id, t.taskname, t.difficulty
from   @users u
       left join @tasks t on u.user_id = t.user_id
where  t.difficulty = (
         select max(x.difficulty)
         from   @tasks x
         where  t.user_id = x.user_id
       )

这将提高性能：

select u.user_id, u.username, t.task_id, t.taskname, t.difficulty
from   @users u
       left join @tasks t on u.user_id = t.user_id
       inner join (
         select x.user_id, max(x.difficulty) as max_difficulty
         from   @tasks x
         group by x.user_id
       ) as y on t.user_id = y.user_id and t.difficulty = y.max_difficulty

这两个查询都返回以下数据集：

user_id     username   task_id     taskname                 difficulty
----------- ---------- ----------- ------------------------ -----------
1           John       3           cook food                3
2           Sally      4           do shopping              2
3           Sam        6           cook food                3

如果用户有两个具有相同难度的最大任务，则查询将包含该用户的两行。

虽然，显示此SQL的查询计划表明，第二个查询的成本几乎是第一个查询成本的两倍。 在where条款中max()似乎比将max()放在from条款中更有效。 我会在您的真实数据上尝试两种方式，看看查询计划/成本适合您。

Answer 2

尝试

    SELECT u.user_id, u.username, t.task_id, t.taskname, m.difficulty
      FROM tasks t
RIGHT JOIN (SELECT user_id, 
                   MAX(difficulty) as difficulty
              FROM tasks
          GROUP BY user_id) m ON t.user_id = m.user_id 
                             AND t.difficulty = m.difficulty
 LEFT JOIN users u ON t.user_id = u.user_id;

Answer 3

我想你正在寻找这样的东西

SELECT u.user_id, u.username, t.task_id, t.taskname,t.difficuilty
FROM users u LEFT JOIN tasks t ON u.user_id = t.user_id
INNER JOIN 
(
 SELECT user_Id,Max(difficuilty) D FROM tasks GROUP BY user_id
)Temp ON Temp.user_id = u.user_id ANDTemp.D = t.difficuilty
GO