mysql - 返回共享共同值但另一列必须匹配多个条件的行

Question

我有一个按 id 和值降序排序的表。我想返回按特定顺序匹配一组键的所有 id。因此，给定 (a5, a3)，我想返回 a 和 b，但不返回 d。

id  value  key
a   3      a5
a   2      a3
a   1      a4

b   4      a5
b   2      a3

c   6      a1
c   2      a2

d   4      a3
d   2      a5

预期输出为

id
a
b

到目前为止，我已经成功匹配 (a5, a3)，但顺序不限。在这里，我返回以任何顺序匹配的所有行和字段；不仅仅是 ID。

SELECT tablename.*
FROM tablename, (SELECT * FROM tablename a
         WHERE key IN ('a5', 'a3')
         GROUP BY id
             HAVING COUNT(*) >= 1) AS result
WHERE tablename.id = result.id

Answer 1

这是集合内集合查询的示例，尽管它比大多数查询要复杂一些。

select id
from tablename t
group by id
having (max(case when "key" = 'a5' then value end) >
        max(case when "key" = 'a3' then value end)
       );

它所做的是找到“a5”和“a3”的值并直接比较它们。如果两者都不存在，则 max(case . . .) 将返回 NULL 并且比较将失败。如果其中一个(或两个)有多个值，则返回最大值。

通过添加更多类似的子句，这应该很容易推广到特定顺序的其他键。这就是为什么我喜欢使用having方法来进行此类查询的聚合——它在很多情况下都有效。

对于您提到的“中间没有任何内容”的情况，我认为这会起作用:

select id
from (select t.*, @rn := @rn + 1 as seqnum
      from tablename t cross join (select @rn := 0) const
      order by key, value
     ) t
group by id
having (max(case when "key" = 'a5' then seqnum end) = max(case when "key" = 'a3' then seqnum end) + 1
       );

附加一个序列号，然后检查它们对于您的两个值是否连续。