mysql - MySQL 5.7 通过相同的 uid 但不同的关键字选择记录

Question

下表结构：

--------------------------------------------------
| ID | UID        | KEYWORD                      |
--------------------------------------------------
| 1 | UID_12345    | AERZTE                      |
| 2 | UID_12345    | RADIOLOGIE                  |
| 3 | UID_12345    | RADIOAKTIV                  |
| 4 | UID_12346    | ARZT                        |
| 5 | UID_12346    | RADIOLOGIE                  |
--------------------------------------------------

现在我将选择关键字所在的两行：AERZTE 和 RADIOLOGIE，并且具有相同的 UID。我怎样才能做到这一点？

标签： mysql

Answer 1

您将需要对所需行进行条件聚合

select *
from test
where keyword in ('AERZTE','RADIOLOGIE')
  and uid in (
    select uid
    from test
    group by uid
    having sum(case when keyword = 'AERZTE' then 1 else 0 end) > 0
      and  sum(case when keyword = 'RADIOLOGIE' then 1 else 0 end) > 0
  )

演示