假设我有一张人员 ID(1-8) 和人员角色(1-4) 的表:

CREATE TABLE personRole (
PersonId int NOT NULL,
RoleId int NOT NULL
);

INSERT INTO personRole
VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 3),
(3, 3),
(4, 3),
(1, 4),
(5, 2),
(6, 1),
(7, 1),
(7, 4),
(8, 1),
(8, 2),
(8, 4)
;

我的目标是选择具有 3 个或更多角色的人员 ID，角色具体为 1、2 和 4。这是我的第一个解决方案:

SELECT PersonId FROM personRole
WHERE RoleID in (1,2,4)
GROUP BY PersonId
HAVING count(*) >= 3

但后来我被告知不要使用 GROUP BY，因为它很慢，所以我想出了这个解决方案:

SELECT distinct PersonId
FROM 
(
  SELECT PersonId, count(*) over(partition by PersonId) AS pcount
  FROM (SELECT * FROM personRole WHERE RoleID in (1,2,4)) AS A
) AS S
WHERE pcount >= 3

我将这些包括在内是为了举例说明我正在努力实现的目标。 但现在我被告知要不计其数地尝试它。我目前能够找到所有具有重复/重复人员 ID 的行:

 SELECT personId
 FROM personRole AS a
 WHERE EXISTS (
   SELECT 1
   FROM   personRole AS a2
   WHERE  a2.PersonId = a.PersonId
   AND    a2.RoleID <> a.RoleID
 );

但我一直在尝试找出如何仅在它们重复 3 次或更多次时才选择它们。如果可以的话，我想我可以将它与以下内容相交:

SELECT PersonId FROM personRole
WHERE RoleID in (1,2,4)

获得我的完整解决方案。到目前为止，我是正确地解决了这个问题，还是走错了方向？

最佳答案

独裁者所说的“没有计数”是指没有聚合函数吗？你总是可以用 sum(1) 而不是 count(*)。

否则，尝试自连接。

select a.PersonId, 
   a.RoleId, 
   b.RoleId, 
   c.RoleId,
   d.RoleId
from personRole a
    left join personRole b
    on a.PersonId = b.PersonId
    and a.RoleId <> b.RoleId
left join personRole c
    on a.PersonId = c.PersonId
    and a.RoleId <> c.RoleId
    and b.RoleId <> c.RoleId
left join personRole d
    on a.PersonId = d.PersonId
    and a.RoleId <> d.RoleId
    and b.RoleId <> d.RoleId
    and c.RoleId <> d.RoleId
order by a.PersonId, a.RoleId
;

+----------+--------+--------+--------+--------+
| PersonId | RoleId | RoleId | RoleId | RoleId |
+----------+--------+--------+--------+--------+
|        1 |      1 |      4 |      2 |   NULL |
|        1 |      1 |      2 |      4 |   NULL |
|        1 |      2 |      4 |      1 |   NULL |
|        1 |      2 |      1 |      4 |   NULL |
|        1 |      4 |      2 |      1 |   NULL |
|        1 |      4 |      1 |      2 |   NULL |
|        2 |      1 |      3 |   NULL |   NULL |
|        2 |      3 |      1 |   NULL |   NULL |
|        3 |      3 |   NULL |   NULL |   NULL |
|        4 |      3 |   NULL |   NULL |   NULL |
|        5 |      2 |   NULL |   NULL |   NULL |
|        6 |      1 |   NULL |   NULL |   NULL |
|        7 |      1 |      4 |   NULL |   NULL |
|        7 |      4 |      1 |   NULL |   NULL |
|        8 |      1 |      2 |      4 |   NULL |
|        8 |      1 |      4 |      2 |   NULL |
|        8 |      2 |      1 |      4 |   NULL |
|        8 |      2 |      4 |      1 |   NULL |
|        8 |      4 |      2 |      1 |   NULL |
|        8 |      4 |      1 |      2 |   NULL |
+----------+--------+--------+--------+--------+
20 rows in set (0.00 sec)

使用在 c.RoleId 中查找值的 where 子句限制它 - 并使用您的魔数(Magic Number)来剔除笛卡尔积，如下所示:

select a.PersonId, 
       a.RoleId, 
       b.RoleId, 
       c.RoleId
from personRole a
left join personRole b
    on a.PersonId = b.PersonId
left join personRole c
    on a.PersonId = c.PersonId
where 
    b.RoleId <> a.RoleId
    and b.RoleId <> c.RoleId
    and c.RoleId <> a.RoleId
    and c.RoleId <> b.RoleId
    and a.RoleId = 1
    and b.RoleId = 2
    and c.RoleId = 4
order by a.PersonId, a.RoleId
;

+----------+--------+--------+--------+
| PersonId | RoleId | RoleId | RoleId |
+----------+--------+--------+--------+
|        1 |      1 |      2 |      4 |
|        8 |      1 |      2 |      4 |
+----------+--------+--------+--------+
2 rows in set (0.00 sec)

如果你想让它更紧凑，而且你只搜索这一种情况，你可以一起取消左连接和值比较

mysql> select a.PersonId, 
    ->        a.RoleId, 
    ->        b.RoleId, 
    ->        c.RoleId
    -> from personRole a,
    ->      personRole b,
    ->      personRole c
    -> where 
    ->     a.PersonId = b.PersonId
    ->     and a.PersonId = c.PersonId
    ->     and a.RoleId = 1
    ->     and b.RoleId = 2
    ->     and c.RoleId = 4
    -> order by a.PersonId, a.RoleId
    -> ;
+----------+--------+--------+--------+
| PersonId | RoleId | RoleId | RoleId |
+----------+--------+--------+--------+
|        1 |      1 |      2 |      4 |
|        8 |      1 |      2 |      4 |
+----------+--------+--------+--------+
2 rows in set (0.00 sec)

关于mysql - SELECT Duplicate/Repeating values with count N 没有 COUNT 或 GROUP BY，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/43619656/