sql—从用户记录较少的表中删除行

9bfwbjaz 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(331)

拥有这张table：

id    |    lat     |    lon     | timestamp  
---------+------------+------------+------------
 5590761 | 41.0106651 | -8.4618407 | 1491071642
 5590761 | 41.0088282 | -8.4613129 | 1491071670
 5590761 | 41.0081011 | -8.4626845 | 1491071763
 5590761 |  41.008415 | -8.4621233 | 1491071767
 5590761 | 41.0084717 |   -8.46211 | 1491071768
 5590761 |  41.008525 | -8.4620333 | 1491071769
 5590761 |   41.00859 | -8.4619783 | 1491071770
 5590761 |  41.008635 |  -8.461875 | 1491071771
 5590761 | 41.0087083 | -8.4616717 | 149107177

有些记录的示例很少。我想删除唯一的所有记录 id 计数小于10。

sql postgresql dml

来源：https://stackoverflow.com/questions/62896872/delete-rows-from-table-with-few-user-records

1条答案

按热度按时间

qcuzuvrc1#

可以在中使用子查询 delete :

delete from t
    where t.id in (select t2.id
                   from t t2
                   group by t2.id
                   having count(*) < 10
                  );

如果您只想删除 select 查询时，可以使用窗口函数：

select t.*
from (select t.*, count(*) over (partition by id) as cnt
      from t
     ) t
where cnt < 10

赞(0）回复(0）举报 2021-07-26

我来回答

sql—从用户记录较少的表中删除行

1条答案

相关问题

热门标签

最新问答