Mysql join 如何查询同时拥有的数据

Question

用户表 table_1

id  name  sex status

1   渣渣徽 1   1    
2   谷田乐 1   1

用户角色等级表 table_2

UID 为表一关联字段

id  uid  level_name  level_id
1     1   青铜        1 
1     1   白银        2
1     2   白银        2
1     2   黄金        3

查询所有青铜等级的用户，这样单条件没问题
SQL

select * from table_1 RIGHT JOIN table_2 ON table_1.id = table_2.uid
where table_1.status = 1 AND table_2.level_id = 1 group by table_1.id

但是如何查询同时拥有青铜与白银的角色呢?
如下查询条件是不行的

select * from table_1 RIGHT JOIN table_2 ON table_1.id = table_2.uid
where table_1.status = 1 AND table_2.level_id = 1 
AND table_2.level_id = 2

此条件该如何查询？

Answer 1

SELECT LEFT(Group_concat(level_id), Length('1,2')) AS gid,
       uid,
       `name`
FROM   table_1 AS a
       LEFT JOIN table_2 AS b
              ON a.id = b.uid
GROUP  BY uid
HAVING gid = '1,2'

Answer 2

select * from table_1 RIGHT JOIN table_2 ON table_1.id = table_2.uid
where table_1.status = 1 AND table_2.level_id = 1 AND table_2.level_id = 2

这段代码意思是同一条table2的记录同时是等级1和等级2，是个假命题。。
改的话需要right join两次table2

select table_1.* from table_1 
RIGHT JOIN table_2 t2Lv1 ON table_1.id = t2Lv1.uid ON t2Lv1.level_id = 1
RIGHT JOIN table_2 t2Lv2 ON table_1.id = t2Lv2.uid ON t2Lv2.level_id = 2
where table_1.status = 1
GROUP BY table_1.id

大致思路就是这样

Answer 3

试试这个查询

SELECT
    t.*
FROM
    (
        SELECT
            uid,
            GROUP_CONCAT(level_id) AS level_ids
        FROM
            `user_roles`
        GROUP BY
            uid
    ) AS t
WHERE
    FIND_IN_SET('1', t.level_ids)
AND FIND_IN_SET('2', t.level_ids);