MYSQL删除所有count(*)=1的结果

2021-12-27 00:00:00 group-by mysql sql-delete

我有一个标记有两个字段 sesskey (varchar32, index) 和 products (int11) 的表，现在我必须删除所有按 sesskey count(*) = 1 分组的行.我尝试了几种方法，但都失败了.

I have a table taged with two fields sesskey (varchar32 , index) and products (int11), now I have to delete all rows that having group by sesskey count(*) = 1. I'm trying a fews methods but all fails.

示例:

delete from taged where sesskey in (select sesskey from taged group by sesskey having count(*) = 1)

sesskey 字段不能是主键，因为它重复了.

The sesskey field could not be a primary key because its repeated.

推荐答案

DELETE  si
FROM    t_session si
JOIN    (
        SELECT  sesskey
        FROM    t_session so
        GROUP BY
                sesskey
        HAVING  COUNT(*) = 1
        ) q
ON      q.sesskey = si.sesskey

您需要在此处加入.使用相关子查询将不起作用.

You need to have a join here. Using a correlated subquery won't work.

有关详细信息，请参阅我博客中的这篇文章:

See this article in my blog for more detail:

  • 保持行

相关文章