mysql - SQL 查询导致 PHPMYADMIN 崩溃 - 查询逻辑问题

Question

由于一个错误，我遇到了一些数据一致性问题。

现在我正在尝试解决这个问题，但不知何故，当我运行部分测试查询时 phpmyadmin 崩溃或在很长一段时间内没有响应。 我尝试单独运行嵌套子查询，它们工作得很好。

基本上我想要解决的问题是:

如何从拥有多个 resource_type=1 且该用户还拥有多个 resource_type=1 的所有用户中删除 geo_address 中的重复 resource_type=1有一个 geo_address 和 resource_type=2

基本上，这意味着如果用户有 2 个 resource_type=1 和 1 个 resource_type=2，那么他就有一个重复的 resource_type=1。如果用户有 2 个 resource_type=1 但没有 resource_type=2，那么它将陷入我在此处 Unresolved 不同情况。

我在 SQL 查询的逻辑上发现了很大的问题

到目前为止我想到的是这个，但我知道它从根本上是错误的，因为它崩溃了

如何正确构建查询？

SELECT member_num
  FROM geo_address2 
 WHERE     id IN (SELECT id 
                    FROM geo_address2 
                   WHERE resource_type=1 
                GROUP BY member_num 
                  HAVING COUNT(resource_type) > 1
        )
       AND id IN (SELECT id 
                    FROM geo_address2 
                   WHERE resource_type=2 
                GROUP BY member_num 
                  HAVING COUNT(resource_type) = 1 
        ) 

Answer 1

经过一番讨论，我编辑了答案。
一种选择是使用此查询:

DELETE 
  FROM geo_address2
 WHERE id IN (SELECT DISTINCT g12.id
                FROM (SELECT member_num, 
                             id
                        FROM geo_address2 
                       WHERE resource_type = 1) g1
              JOIN 
                     (SELECT member_num, 
                             id
                        FROM geo_address2 
                       WHERE resource_type = 1) g12
                   ON g1.member = g12.member AND g1.id > g12.id
                     (SELECT member_num, 
                             id
                        FROM geo_address2 
                       WHERE resource_type = 2) g2
                   ON g1.member_num = g2.member_num
               );

第二个(我相信逻辑上是一样的):

DELETE 
  FROM geo_address2
 WHERE id IN (
            SELECT g1.id
              FROM geo_address2 g0
                   JOIN geo_address2 g1
                        ON (g0.member_num = g1.member_num 
                            AND g0.id > g1.id
                            AND g0.resource_type = 1
                            AND g1.resource_type = 1)
                   JOIN geo_address2 g2
                        ON (g1.member_num = g2.member_num
                            AND g0.resource_type = 1
                            AND g2.resource_type = 2)
               );