mysql - 如何将 ORDER 和 LIMIT 应用于 IN() 子句中的每个子查询？

Question

需要使用多个 WHERE 参数选择行，但只返回每个条件的最新行；更喜欢用一条 SQL 语句访问数据库，而不是使用循环。

表格

虚拟数据:

id  first   last    
------------------------
1   jeff    jones
2   homer   simpson
3   john    doe
4   jeff    jones
5   jeff    jones
6   sam     smith
7   homer   simpson
8   john    doe

SQL

我当前的sql语句:

SELECT * FROM members
WHERE (first,last) IN (('jeff','jones'), ('homer','simpson')) 
ORDER BY id DESC LIMIT 1

需要结果

从每个子查询返回一行，显示最新条目，即最高 ID:

id  first   last    
------------------------
5   jeff    jones
7   homer   simpson

提前致谢。

Answer 1

通过使用first, last 进行分组，每个组合你会得到一个结果:

SELECT MAX(id), first, last
  FROM members
 WHERE (first,last) IN (('jeff','jones'), ('homer','simpson')) 
 GROUP BY first, last
 ORDER BY id;

MAX(id) 选择每个 first/last 组合的最大 id。之后按 id 排序。

检查这个Fiddle demo .

p.s.:根据评论添加。注意:您不能在子查询中使用 LIMIT。所以使用 JOIN:

SELECT sup.id, sup.age, sup.first, sup.last 
  FROM members sup
  JOIN ((SELECT id, age, first, last -- choose first two Homers
           FROM members
          WHERE (first,last) IN (('homer','simpson'))
          ORDER BY id DESC 
          LIMIT 2)
          UNION 
        (SELECT id, age, first, last -- unify with first two Jeffs
           FROM members
          WHERE (first,last) IN (('jeff','jones')) 
          ORDER BY id DESC 
          LIMIT 2)) sub              -- left join 
 WHERE sup.id    = sub.id            -- and select only entries from sub
   AND sup.age   = sub.age
   AND sup.first = sub.first
   AND sup.last  = sub.last;

检查这个Fiddle here .