mysql – SELECT重复/重复值,计数N不带COUNT或GROUP BY
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – SELECT重复/重复值,计数N不带COUNT或GROUP BY，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4246字，纯文字阅读大概需要7分钟。
内容图文
假设我有一个人员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)
获得我的完整解决方案.到目前为止我是否正确地解决了这个问题,还是我朝错误的方向前进？
解决方法:
通过“不计数”,独裁者是否意味着没有集合函数？你总是可以求和(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子句限制它 – 并使用您的幻数来剔除笛卡尔积,如下所示：
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重复/重复值,计数N不带COUNT或GROUP BY全部内容，希望文章能够帮你解决mysql – SELECT重复/重复值,计数N不带COUNT或GROUP BY所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。