如何根据行在表中出现的次数来选择行？

发布于 2024-12-11 10:16:30 字数 876 浏览 0 评论 0原文

我有我的表格：称之为 tblA 该表格有三行：id、sub-id 和 visibility

sub-id 是主键（它定义 id 的分类法）。我正在尝试构建一个查询，选择每个出现次数少于 3 次的 id。

这是一个示例查询/结果

select * from tbla where id = 188002;

+--------+--------+-------------+
| sub-id |  id    | visibility  |
+--------+--------+-------------+
| 284922 | 188002 |      2      |
| 284923 | 188002 |      2      |
| 284924 | 188002 |      0      |
+--------+--------+-------------+

从我所看到的此处和这里看起来我需要加入桌子本身。我真的不明白这能实现什么。

如果有人对此有见解，我们将不胜感激。我将继续研究它，并用我遇到的任何其他信息更新这个主题。

谢谢

原文

I have my table: call it tblA THis table has three rows, id, sub-id, and visibility

sub-id is the primary key (it defines taxonomies for id). I'm trying to build a query that selects every id that appears less than three times.

here is an example query/result

select * from tbla where id = 188002;

+--------+--------+-------------+
| sub-id |  id    | visibility  |
+--------+--------+-------------+
| 284922 | 188002 |      2      |
| 284923 | 188002 |      2      |
| 284924 | 188002 |      0      |
+--------+--------+-------------+

From what i've seen here and here it looks like I need to join the table on...itself. I dont really understand what that accomplishes.

If anyone has insight into this, it is appreciated. I will continue to research it and update this topic with any additional information I come across.

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

茶花眉 2024-12-18 10:16:30

SELECT id
    FROM tbla
    GROUP BY id
    HAVING COUNT(*) < 3

SELECT id
    FROM tbla
    GROUP BY id
    HAVING COUNT(*) < 3

回复收藏 0 原文

风情万种。 2024-12-18 10:16:30

如果您想从表中选择所有列，则必须在子选择中使用 @Joe 的查询：

SELECT * FROM tbla a
WHERE a.id IN (SELECT DISTINCT b.id 
               FROM tbla b 
               GROUP BY b.id 
               HAVING COUNT(*) < 3)

此查询首先选择所有重复项少于 3 个的 id。
不同的消除了重复，查询的工作方式相同，但速度稍慢。

接下来，它选择 id 满足子选择条件的所有行，即重复 id 少于 3 个的行。

您无法一次性执行此操作的原因是 group by 将具有相同 id 的所有行堆在一起，形成一个超级行 < em>（因为需要更好的元数据） .
您无法分离出不在 group by 子句中的列。

外部选择解决了这个问题。

If you want to select all columns from the table, you will have to use @Joe's query in a sub-select:

SELECT * FROM tbla a
WHERE a.id IN (SELECT DISTINCT b.id 
               FROM tbla b 
               GROUP BY b.id 
               HAVING COUNT(*) < 3)

This query first selects all id's that have fewer than 3 duplicates.
The distinct eliminates duplicates, the query works the same without, but slightly slower.

Next it selects all rows that have an id that meets the criteria in the sub-select i.e. that have fewer than 3 duplicate id's.

The reason that you cannot do this in one go is that the group by heaps all rows with the same id together into one super-row (for want of a better metafor) .
You cannot separate out the columns that are not in the group by clause.

The outer select solves this.

回复收藏 0 原文

~没有更多了~