mysql - 如何检查具有多值的列

Question

我有两个表，例如1:-用户

id  name
1   vivek
2   raj
3   mohan
4   gagan
5   priya

2:-购买

id  typee        user_id
1   class        1
2   class        1
3   course       1
4   class        2
5   test_series  3
6   test_series  2 

我想搜索那些从类(class)或类(class)中购买了一个并且还购买了 test_series 的学生

Answer 1

您可以尝试以下查询 -

SELECT u.name
FROM purchase p1
join users u on p1.user_id = u.id
join (select distinct user_id
      from purchase
      where typee = 'test_series') p2 on p1.user_id = p2.user_id
where typee in ('class', 'course')
GROUP BY user_id
having count(distinct typee) >= 1