我想为“student”类型的每个用户返回一行,显示他们的“name”和最新的“score”(按时间倒序排列)。
我有两张桌子用户和服务
用户表

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

但我意识到,如果日期相同,并且每个用户只能返回一行,那么我最终会得到重复的数据(而双重分组不起作用)。
我想我已经把这件事搞得太复杂了,所以一条生命线是非常值得的。

最佳答案

尝试:

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

关于mysql - MySQL的内联接,最大和不同,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26014852/

10-08 23:16