mysql - 仅选择给定输入的最大 id

Question

我有以下sql查询。max(v.id)我只想v.id为给定的p_id.

你能帮我么？

SELECT
    v.id,
    u.firstname as author_name,
    u.lastname as author_surname,
FROM ps_versioning as v
INNER JOIN u_users as u ON u.user_id = v.user_id
where v.patient_id = p_id

除了公认的解决方案外，我还找到了另一种使用order by v.id desc Limit 1. 请参阅下面的完整示例：

SELECT
    v.id,
    u.firstname as author_name,
    u.lastname as author_surname,
FROM ps_versioning as v
INNER JOIN u_users as u ON u.user_id = v.user_id
where v.patient_id = p_id
order by v.id desc Limit 1

标签： mysql

Answer 1

这将完成这项工作：

SELECT
    v.id,
    u.firstname as author_name,
    u.lastname as author_surname,
FROM ps_versioning as v
INNER JOIN u_users as u ON u.user_id = v.user_id
where v.patient_id = p_id
AND v.id = (SELECT MAX(id) FROM ps_versioning GROUP BY patient_id)