从符合一个条件的表中删除，其中同一个表中的行匹配不同的条件？

Sorry for the mega title... I was trying to be descriptive enough. I've got a table that contains event attendance data that has some erroneous data in it. The table definition is kind of like this:

对不起巨型标题......我试图描述得足够多。我有一个包含事件出勤数据的表,其中包含一些错误数据。表定义有点像这样:

id (row id)
date
company_name
attendees

It ended up with some cases where for a given date, there are two entries matching a company_name and date but one has attendees=0 and the other has attendees>0. In those cases, I want to discard the ones where attendees=0.

最后,在某些情况下,对于给定日期,有两个条目匹配company_name和date,但其中一个参与者= 0,另一个参与者> 0。在这些情况下,我想丢弃参与者= 0的那些。

I know you can't join on the same table while deleting, so please consider this query to be pseudocode that shows what I want to accomplish.

我知道你在删除时无法加入同一个表,所以请将此查询视为伪代码,以显示我想要完成的内容。

DELETE FROM attendance a WHERE a.attendees=0 AND a.date IN (SELECT b.date FROM attendance b WHERE b.attendees > 0 AND b.company_name = a.company_name);

I also tried to populate a temporary table with the ids of the rows I want to delete, but that query hangs because of the IN (SELECT ...) clause. My table has thousands of rows so that just maxes out the CPU and then times out.

我还尝试使用我想要删除的行的id来填充临时表,但该查询由于IN(SELECT ...)子句而挂起。我的表有几千行,所以只是最大化CPU然后超时。

1 个解决方案

#1

This ugly thing should work (using alias permit to avoid the You can't specify target table for update in FROM clause error)

这个丑陋的东西应该工作(使用别名允许避免你不能在FROM子句错误中指定更新的目标表)

DELETE FROM attendance
WHERE (attendees, date, company_name)
IN (SELECT c.a, c.d, c.c 
    FROM
     (SELECT MIN(attendees) a, date d, company_name c
      FROM attendance
      GROUP BY date, company_name
      HAVING COUNT(*) > 1) as c);

SqlFiddle