英文:
How to retrieve rows with group count > 1
问题
我想检索所有行，其中一个组的计数> 1，不使用子查询。如果使用子查询，它将是
SELECT *
FROM abc
LEFT JOIN
(SELECT a, COUNT() AS cnt
FROM abc
GROUP BY a
HAVING COUNT() > 1) aa
ON abc.a = aa.a
WHERE aa.a IS NOT NULL
这是表格：
| a | b |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 4 |
| 3 | 3 |
| 4 | 4 |
结果将是
| a | b |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 2 | 3 |
| 2 | 4 |
英文:
I want to retrieve all the rows which the count of a group > 1 without subquery. If subquery used, it will be
SELECT *
FROM abc
LEFT JOIN
(SELECT a, COUNT(*) AS cnt
FROM abc
GROUP BY a
HAVING COUNT(*) > 1) aa
ON abc.a = aa.a
WHERE aa.a IS NOT NULL
Here is the table:
|a|b|
|-|-|
|1|2|
|1|3|
|2|3|
|2|4|
|3|3|
|4|4|
The result would be
|a|b|
|-|-|
|1|2|
|1|3|
|2|3|
|2|4|
答案1
得分: 2
一个我经常在这种情况下使用的方法是自连接。大致如下：
SELECT a.*
FROM abc a
INNER JOIN abc b ON a.a = b.a
WHERE a.b <> b.b;
它的优点是非常易读，通常比具有子查询的查询要快得多。
英文:
An approach that I often use in such cases is a self join. Something along these lines:
SELECT a.*
FROM abc a
INNER JOIN abc b ON a.a = b.a
WHERE a.b <> b.b;
It has the advantage of being eminently readable, and usually considerably quicker to type than a query with sub-query.
答案2
得分: 2
只使用窗口函数！
选择 a, b
从 (
选择 t.*, count(*) over(partition by a) cnt
从 abc t
) t
其中 cnt > 1
窗口计数根据 a 对数据集进行分区，然后计算当前分区中的记录总数。然后，我们可以使用这些信息来筛选数据集。
英文:
Just use window functions!
select a, b
from (
select t.*, count(*) over(partition by a) cnt
from abc t
) t
where cnt > 1
The window count partitions the dataset by a, then counts the overall number of records in the current partition. We can then use this information to filter the dataset.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论