鍍金池/ 問答/PHP  數(shù)據(jù)庫/ Mysql join 如何查詢同時(shí)擁有的數(shù)據(jù)

Mysql join 如何查詢同時(shí)擁有的數(shù)據(jù)

用戶表 table_1

id  name  sex status

1   渣渣徽 1   1    
2   谷田樂 1   1

用戶角色等級(jí)表 table_2

UID 為表一關(guān)聯(lián)字段

id  uid  level_name  level_id
1     1   青銅        1 
1     1   白銀        2
1     2   白銀        2
1     2   黃金        3

查詢所有青銅等級(jí)的用戶，這樣單條件沒問題
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

但是如何查詢同時(shí)擁有青銅與白銀的角色呢?
如下查詢條件是不行的

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

此條件該如何查詢？

回答
編輯回答
你好胸
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的記錄同時(shí)是等級(jí)1和等級(jí)2，是個(gè)假命題。。
改的話需要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

大致思路就是這樣

2018年2月21日 02:15
編輯回答
不討喜

試試這個(gè)查詢

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);
2018年2月16日 09:07
編輯回答
尐飯團(tuán)
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'

clipboard.png

2018年6月25日 17:47