选择 id WHERE COUNT(*) > X ? - 如何从表中拥有多于 X 条记录的任何用户获取记录?

发布于 2024-10-15 10:32:50 字数 147 浏览 4 评论 0原文

显然标题中的查询不起作用,但它可能以一种天真的方式说明了我想要做什么。我有一个表,其中包含一些由 id 列标识的用户。该 ID 在数据库中不是唯一的。它标记了我的表中可能有多个记录的用户。

如何显示表中拥有超过 10 条记录的所有用户(由 id 标识)的完整记录?

Obviously the query in the title does not work, but it might illustrate in a naive way, what I would like to do. I have a table that contains some users identified by an id column. This id is NOT unique within the database. It marks a user that may have multiple records in my table.

How can I show the whole record of all users (identified by id) that have more than 10 records in my table?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(4

且行且努力 2024-10-22 10:32:50

使用having代替where

SELECT id
  FROM (
        SELECT id, COUNT(*) as cnt
        FROM somewhere 
        GROUP BY id
        HAVING cnt > 1
   ) temp_table

Use having instead of where:

SELECT id
  FROM (
        SELECT id, COUNT(*) as cnt
        FROM somewhere 
        GROUP BY id
        HAVING cnt > 1
   ) temp_table
楠木可依 2024-10-22 10:32:50
SELECT * FROM user 
WHERE id IN (SELECT id FROM user GROUP BY id HAVING COUNT(*) > 10)
SELECT * FROM user 
WHERE id IN (SELECT id FROM user GROUP BY id HAVING COUNT(*) > 10)
温暖的光 2024-10-22 10:32:50
SELECT id, COUNT(*) FROM Table GROUP BY id HAVING COUNT(*) > 10
SELECT id, COUNT(*) FROM Table GROUP BY id HAVING COUNT(*) > 10
撩人痒 2024-10-22 10:32:50

方法如下:

 CREATE TABLE mytable_clean AS
 SELECT * FROM mytable WHERE id IN(
 SELECT id FROM
 (SELECT id,COUNT(*) AS appearance 
 FROM mytable
 GROUP BY id) AS id_count
 WHERE id_count.appearance > 9 )

它确实有效。它并不慢,但对我来说看起来有点笨拙。欢迎更好的解决方案:)

here is how:

 CREATE TABLE mytable_clean AS
 SELECT * FROM mytable WHERE id IN(
 SELECT id FROM
 (SELECT id,COUNT(*) AS appearance 
 FROM mytable
 GROUP BY id) AS id_count
 WHERE id_count.appearance > 9 )

It does work. It's not to slow, but looks a little clumsy to me. Better solutions welcome :)

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文