MySQL JOIN 和 DELETE 仅在第一次出现

Question

我必须从 MySQL 表中删除 4000 万行。

我必须找到所有有输出的行 -> "STATIC OUTPUT"然后删除具有上述输出的行并删除具有相同主机的下一行以上输出和输出值除“STATIC OUTPUT”以外的服务。

示例数据:

列 -> id, host, service, output

1,"127.0.0.1","service1","STATIC OUTPUT"
2,"127.0.0.2","service5","RANDOM OUTPUT X0"
3,"127.0.0.2","service5","STATIC OUTPUT"
4,"127.0.0.3","service1","RANDOM OUTPUT X1"
5,"127.0.0.3","service10","RANDOM OUTPUT X2"
6,"127.0.0.2","service5","RANDOM OUTPUT X3"
7,"127.0.0.1","service2","RANDOM OUTPUT X4"
8,"127.0.0.1","service1","RANDOM OUTPUT X5"
9,"127.0.0.2","service4","RANDOM OUTPUT X6"
10,"127.0.0.3","service10","RANDOM OUTPUT X7"
11,"127.0.0.1","service1","RANDOM OUTPUT X7"
12,"127.0.0.1","service1","RANDOM OUTPUT X8"
13,"127.0.0.1","service1","RANDOM OUTPUT X9"
14,"127.0.0.2","service5","RANDOM OUTPUT X10"
15,"127.0.0.1","service1","STATIC OUTPUT"
16,"127.0.0.1","service1","RANDOM OUTPUT X11"
17,"127.0.0.1","service1","RANDOM OUTPUT X12"    
...

例子:当我们发现

1,"127.0.0.1","service1","STATIC OUTPUT"

我们应该删除id值为1和8的行，

8,"127.0.0.1","service1","RANDOM OUTPUT X5"

当我们发现

3,"127.0.0.2","service5","STATIC OUTPUT"

我们应该删除id值为3和6的行，

6,"127.0.0.2","service5","RANDOM OUTPUT X3"

我写了这样的东西(SELECT intead of DELETE statement because of test query)，

SELECT * FROM data r1 INNER JOIN (SELECT id, host, service 
FROM data 
WHERE output = 'STATIC OUTPUT') r2 ON 
      r1.id>r2.id AND r1.service=r2.service 
      AND r1.host=r2.host 
      AND r1.output<>'STATIC OUTPUT' 
GROUP BY r1.host, r1.service

但我认为这是一种错误的方式。

MySQL 5.1.73

Answer 1

更正

现在应该这样做了!

SELECT min(sp.id) as id FROM
(SELECT hs.id, hs.host, hs.service, hs.output, so.id as soid
FROM data hs
INNER JOIN
(SELECT id,host,service,output FROM data 
WHERE output = "STATIC OUTPUT") so
ON so.host = hs.host and so.service = hs.service
AND hs.id > so.id WHERE hs.output <> "STATIC OUTPUT") sp
group by host,service, soid
UNION 
SELECT id FROM data WHERE output = "STATIC OUTPUT";