mySql INNER JOIN，MAX＆DISTINCT

问题描述：

我正在寻找每个显示“名称”及其最新“分数”（按照时间顺序倒序）的“学生”类型用户返回一行。mySql INNER JOIN，MAX＆DISTINCT

我有两个表的用户&服务

用户表

id name type 
---|-------|----- 
1 | Bob | student 
2 | Dave | student 
3 | Larry | student 
4 | Kevin | master

服务表

id score userId date 
---|--------|-------|------------ 
1 | 14  | 1  | 2014-09-04 
2 | 99  | 3  | 2014-09-03 
3 | 53  | 2  | 2014-09-07 
4 | 21  | 1  | 2014-09-08 
5 | 79  | 2  | 2014-09-08 
6 | 43  | 3  | 2014-09-10 
7 | 72  | 3  | 2014-09-10 
8 | 66  | 2  | 2014-09-01 
9 | 43  | 3  | 2014-08-22 
10 | 26  | 1  | 2014-08-22

所需的结果

id scores name date 
---|--------|-------|------------ 
3 | 43  | Larry | 2014-09-10 
1 | 21  | Bob | 2014-09-08 
2 | 79  | Dave | 2014-09-08

瓦在我曾尝试的是：

SELECT users.id, users.name, services.date, services.score 
FROM users 
JOIN services ON users.id = services.userId 
WHERE users.type='student' 
ORDER BY services.date DESC

但是，这总是返回每个用户表中的最后日期。

所以我决定尝试从另一端是这样处理的：

SELECT servicesTemp.date, servicesTemp.score 
FROM services servicesTemp 
INNER JOIN 
    (SELECT userId, MAX(date) AS MaxExpDate 
    FROM services 
    GROUP BY clientId) servicesTempGrp 
ON servicesTemp.userId = servicesTempGrp.userId 
AND servicesTemp.MaxDate = servicesTempGrp.MaxDate

但意识到，我最终会重复，如果日期是都一样，我只能返回每一个行用户（并且双重分组不起作用）。

我想我现在已经过了复杂化，所以一条生命线会非常感激。

你怎么43和72之间选择作为得分拉里？ – Arth 2014-09-24 10:50:25

在这种情况下，只要返回一个，它并不重要。 – 2014-09-24 10:52:28

@TraceyTurn：是的，因为你的'日期'和'分数'不同步...你是否只是*任何*'分数'返回？ – NoobEditor 2014-09-24 10:57:33

答

尝试：

SELECT users.id, users.name, services.date, services.score 
FROM users 
JOIN services ON users.id = services.userId 
WHERE users.type='client' 
AND services.date = (SELECT MAX(date) from services where userID = users.id) 
ORDER BY services.date DESC

完美，谢谢。 – 2014-09-24 11:25:22

答

您可以通过使用substring_index()/group_concat()招保证一行：

SELECT u.id, u.name, max(s.date) as date, 
     substring_index(group_concat(s.score order by date desc), ',', 1) as score 
FROM users u JOIN 
    services s 
    ON u.id = s.userId 
WHERE u.type = 'client' 
GROUP BY u.id, u.name 
ORDER BY s.date DESC;

不使用group by，为每个用户只得到了一排另一种选择是使用变量。或者，如果你知道的ID被按顺序分配，使用的id代替date：

SELECT u.id, u.name, s.date, s.score 
FROM users u INNER JOIN 
    services s 
    on u.userId = s.userId INNER JOIN 
    (SELECT userId, MAX(id) AS MaxId 
     FROM services 
     GROUP BY userId 
    ) smax 
    ON s.userId = smax.userId and s.Id = smax.MaxId 
WHERE u.type = 'client';

mySql INNER JOIN，MAX＆DISTINCT

相关推荐