MySQL删除除一个

Question

我有以下数据库结构:

id   idproperty    idgbs
 1    1             136
 2    1             128       
 3    1             10
 4    1             1
 5    2             136
 6    2             128
 7    2             10
 8    2             1
 9    3             561
10    3             560
11    3             10
12    3             1
13    4             561
14    4             560
15    4             10
16    4             1
17    5             234
18    5             120
19    5             1
20    6             234
21    6             120
22    6             1

详情如下:

表格引用不同地理位置的idproperty。例如:

idgbs

  1 refers to United States
 10 refers to Alabama with parentid 1 (United States)
128 refers to Alabama Gulf Coast with parentid 10 (Alabama)
136 Dauphin Island with parentid 128 (Alabama Gulf Coast)

所以，结构是:

United States > Alabama > Alabama Gulf Coast > Dauphin Island

我想删除 idproperty 的所有条目，除了第一个带有 idgbs 136、128、10、1 集的条目，即在所有 GBS 中至少保留 1 个属性并删除其他条目。

另外，地理条目有时是4级，有时是3级。

请分享逻辑和 SQL 查询以删除除每个唯一 GBS 中的一个条目之外的所有条目。

GBS 1, 10, 128, 136 是唯一的，所以数据库应该只包含 1 个具有这些 GBS 的属性 id。

查询后的表如下所示:

id   idproperty    idgbs
 1    1             136
 2    1             128       
 3    1             10
 4    1             1
 9    3             561
10    3             560
11    3             10
12    3             1
17    5             234
18    5             120
19    5             1

改写问题:

我想在每个根级别 GBS 中保留属性，即 Dauphin Island 中应该只有一个属性。

Answer 1

哇...我想我明白你现在想要什么了。我不能放过这个 ;-)

我必须意识到在这个问题中，您想要删除属性 2，因为它与属性 1 共享一个层次结构。一旦我意识到这一点，我就有了以下想法。基本上，我们两次加入 self 的聚合版本:第一个告诉我们我们的“gbs 层次结构路径”是什么，第二个匹配所有 previous 属性 层次结构。发现没有共享其层次结构的“先前”属性的行将被保留，其余具有该层次结构的行将被删除。这可能会进一步调整，但我想现在就分享一下。我已经用您显示的数据对其进行了测试，并得到了您发布的结果。

DELETE 
  each_row.*
FROM property_gbs AS each_row

JOIN ( SELECT 
         idproperty, 
         GROUP_CONCAT(idgbs ORDER BY idgbs DESC SEPARATOR "/") AS idgbs_path 
       FROM property_gbs 
       GROUP BY idproperty
     ) AS mypath 
     USING(idproperty)

LEFT JOIN ( SELECT 
              idproperty, 
              GROUP_CONCAT(idgbs ORDER BY idgbs DESC SEPARATOR "/") AS idgbs_path 
            FROM property_gbs  
            GROUP BY idproperty
          ) AS previous_property 
      ON mypath.idgbs_path = previous_property.idgbs_path 
  AND previous_property.idproperty < each_row.idproperty

WHERE previous_property.idproperty

请注意，最后一行不是拼写错误，我们只是检查是否存在具有相同路径的先前属性。如果有，则删除当前评估的行。

干杯!

说明说明

这里的想法是将每一行与其层次结构相关联，即使它是代表层次结构中间某处的行(例如问题中的行:{2, 1, 128})。通过第一次连接到聚合，每一行现在“知道”它的路径是什么(因此该行将获得“136/128/10/1”)。然后，我们可以在第二个连接中使用该值来查找具有相同路径的其他属性，但前提是它们具有较低的属性 ID。这使我们能够检查是否存在具有相同“路径”的低 ID 属性，并删除代表确实具有此类“低阶路径兄弟”的属性的任何行。