如何选择所有的最新记录所有用户的
问题描述：
我有一个预约表，我需要选择为所有用户的最新记录，目前我的查询返回的最古老的每个用户ID，而不是最新的一个每个如何选择所有的最新记录所有用户的
委任表格
+-------+--------------+--------------+--------------+
| ID | time | userid | description |
+-------+--------------+--------------+--------------+
| 1 | 2017-10-20 | 4 | etc |
+-------+--------------+--------------+--------------+
| 2 | 2017-10-21 | 6 | etc |
+-------+--------------+--------------+--------------+
| 3 | 2017-10-22 | 7 | etc |
+-------+--------------+--------------+--------------+
| 4 | 2017-10-23 | 8 | etc |
+-------+--------------+--------------+--------------+
| 5 | 2017-10-24 | 6 | etc |
+-------+--------------+--------------+--------------+
| 6 | 2017-10-25 | 7 | etc |
+-------+--------------+--------------+--------------+
用户表
+-------+--------------+--------------+--------------+
| ID | first | last | status |
+-------+--------------+--------------+--------------+
| 4 | jo | do | 1 |
+-------+--------------+--------------+--------------+
| 6 | jid | did | 1 |
+-------+--------------+--------------+--------------+
| 7 | jone | done | 1 |
+-------+--------------+--------------+--------------+
| 8 | ja | da | 1 |
+-------+--------------+--------------+--------------+
当前查询
$sql = "SELECT *
FROM appointment
LEFT JOIN users AS user
ON user.id = appointment.userid
WHERE user.status = 1
GROUP BY appointment.userid
";
当前结果
+-------+--------------+--------------+--------------+
| ID | time | userid | description |
+-------+--------------+--------------+--------------+
| 1 | 2017-10-20 | 4 | etc |
+-------+--------------+--------------+--------------+
| 2 | 2017-10-21 | 6 | etc |
+-------+--------------+--------------+--------------+
| 3 | 2017-10-22 | 7 | etc |
+-------+--------------+--------------+--------------+
| 4 | 2017-10-23 | 8 | etc |
+-------+--------------+--------------+--------------+
预期输出
+-------+--------------+--------------+--------------+
| ID | time | userid | description |
+-------+--------------+--------------+--------------+
| 1 | 2017-10-20 | 4 | etc |
+-------+--------------+--------------+--------------+
| 4 | 2017-10-23 | 8 | etc |
+-------+--------------+--------------+--------------+
| 5 | 2017-10-24 | 6 | etc |
+-------+--------------+--------------+--------------+
| 6 | 2017-10-25 | 7 | etc |
+-------+--------------+--------------+--------------+
答
试试这个
SELECT a.ID, a.time, a.userid, a.description
FROM users u
INNER JOIN appointment a ON u.id = a.userid
WHERE u.status = 1
AND time in(SELECT MAX(time) from appointment t WHERE t.userid = a.userid)
答
SELECT max(appointment.id), max(time), user.id, description
FROM appointment
LEFT JOIN users AS user
ON user.id = appointment.userid
WHERE user.status = 1
GROUP BY appointment.userid
+0
这个答案是错的，而左连接将改造成一个内部联接，因为你'WHERE'条款。 – sagi
答
您可以自连接至IDENT IFY的最新任命：
SELECT s.* FROM (
SELECT *
FROM appointment
LEFT JOIN users AS user
ON user.id = appointment.userid and user.status = 1) s
LEFT JOIN appointment p
ON(p.userid = s.userid and p.time < s.time)
WHERE p.time IS NULL
虽然我不明白为什么要LEFT JOIN表users。
答
SELECT MAX(a.ID) ID, MAX(a.time) time, a.userid, MAX(a.description) description
FROM appointment a
INNER JOIN users u ON u.id = a.userid
WHERE u.status = 1
GROUP BY a.userid
ORDER BY a.ID
输出
ID time userid description
1 2017-10-20T00:00:00Z 4 etc
5 2017-10-24T00:00:00Z 6 etc
6 2017-10-25T00:00:00Z 7 etc
4 2017-10-23T00:00:00Z 8 etc
答
我觉得left join与用户表是不必要的。 下面的查询将产生预期的结果
select a1.* from appointment a1
inner join (select max(id) as id ,userid from appointment group by userid) a2
on a1.id=a2.id
你有一组通过，但没有聚集函数.. – Randy