如何使用SQL语句求出交集？

比如，现在有下面这张表：

id	pet	user
1	cat	userA
2	dog	userA
3	pig	userB
4	bird	userD

怎么写SQL语句能选出下面的数据：

id	pet	user
1	cat	userA
3	cat	userC

其中， userA和userC是传递的参数。

我想实现的是，求出多个用户共同喜欢那些宠物，大神们，帮帮忙吧，我用的是Mysql数据库。

若不是MySQL，我可能会这样做：

SELECT PET FROM TB_PET WHERE USER='userA'
    INTERSECT
SELECT PET FROM TB_PET WHERE USER='userC'

很遗憾的，MySQL并没有提供INTERSECT和MINUS。
仍然有很多办法来实现这一效果。

比如：

EXISTS

 SELECT PET FROM PET_A PET_USERA WHERE USER = 'userA' 
    AND EXISTS(SELECT PET FROM PET_A PET_USERC WHERE PET_USERC.USER = 'userC' AND                 PET_USERA.PET=PET_USERC.PET)

IN

SELECT PET FROM PET_A WHERE USER = 'userA' AND PET IN (SELECT PET FROM PET_A WHERE USER = 'userC')

或者说按题主要求的结构

SELECT usera.* FROM PET_A usera 
    inner JOIN PET_A userc ON usera.PET = userc.PET
WHERE usera.USER IN ( 'userA','userC') AND usera.`USER` <> userc.`USER` ORDER BY ID

SELECT
ID,PET,USER
FROM
TABLE
WHERE
PET IN (
SELECT
TB.PET
FROM
TABLE TB
GROUP BY
TB.PET
HAVING COUNT(TB.PET) >1
)

试一下吧

SELECT 
    *
FROM
    `table` as `t1`
WHERE
    EXISTS( SELECT 
            *
        FROM
            `table` as `t2`
        WHERE
            `t1`.`id` = `t2`.`id`
                AND `id` IN ('userA' , 'userB'))
GROUP BY `pet`
HAVING COUNT(`pet`) = 2;

第一想到的是子查询

select * from tb where pet in (select pet from tb where user = 'userA') and user = 'userC'

SELECT *
FROM tbl_pet
WHERE pet=(
SELECT pet
FROM tbl_pet p
WHERE USER IN('zhangsan','wangwu')
GROUP BY pet
HAVING COUNT(pet)>=2)
AND USER IN('zhangsan','wangwu')

【热门文章】