查找包含所有链接行的行

Question

我有两个表：

User (id, name, etc)
UserRight (user_id, right_id)

我想找到拥有权限 1、2 和 3 的用户，但没有只拥有其中一两个权限的用户。此外，权限的数量也会有所不同，因此搜索 (1,2,3) 和 (1,2,3,4,5,6,7) 应该适用于大致相同的查询。

本质上：

SELECT *
FROM User
WHERE (
    SELECT right_id 
    FROM tblUserRight 
    WHERE user_id = id 
    ORDER BY user_id ASC
) = (1,2,3)

这在 MySQL 中可能吗？

Answer 1

SELECT u.id, u.name ...
FROM User u
JOIN UserRight r on u.id = r.user_id
WHERE right_id IN (1,2,3)
GROUP BY u.id, u.name ...
HAVING COUNT DISTINCT(right_id) = 3

Answer 2

您还可以使用 PIVOT 来执行此操作，特别是如果您需要视觉表示。我在 SQL Server 上做了这个 - 你也许可以翻译它。

Declare @User Table (id  Int, name Varchar (10))
Declare @UserRight Table (user_id Int, right_id Int)

Insert Into @User Values (1, 'Adam')
Insert Into @User Values (2, 'Bono')
Insert Into @User Values (3, 'Cher')

Insert Into @UserRight Values (1, 1)
Insert Into @UserRight Values (1, 2)
Insert Into @UserRight Values (1, 3)
--Insert Into @UserRight Values (2, 1)
Insert Into @UserRight Values (2, 2)
Insert Into @UserRight Values (2, 3)
Insert Into @UserRight Values (3, 1)
Insert Into @UserRight Values (3, 2)
--Insert Into @UserRight Values (3, 3)


SELECT *
FROM @User U
    INNER JOIN @UserRight UR
        ON U.id = UR.User_Id
PIVOT
(
    SUM (User_Id)
    FOR Right_Id IN ([1], [2], [3])
) as xx
WHERE 1=1


SELECT *
FROM @User U
    INNER JOIN @UserRight UR
        ON U.id = UR.User_Id
PIVOT
(
    SUM (User_Id)
    FOR Right_Id IN ([1], [2], [3])
) as xx
WHERE 1=1
AND [1] IS NOT NULL
AND [2] IS NOT NULL
AND [3] IS NOT NULL

Answer 3

与我的答案中指出的错误相对应，这里有一个带有计数和子查询的解决方案：

SELECT *
FROM User
WHERE 3 = (
    SELECT Count(user_id)
    FROM tblUserRight 
    WHERE right_id IN (1,2,3)
    AND user_id = User.id
)

优化器当然可以将其更改为马丁·史密斯的解决方案（即通过使用分组依据）。