如何排除 SQL 树中匹配连接的行

Question

更新：在尝试了几个小时后，采用了多查询解决方案，并使用仅包含父属性的表来确定哪些项目需要更新。

---

抱歉，标题不好，我不知道如何简洁地描述这个问题。

我有一组应与属性具有一对一关系的项目。

我有一个查询返回数据错误且这种关系已被破坏的那些行（一对多）。我正在收集这些行来修复它们并恢复这种一对一的关系。

这是我的实际问题的理论上的简化，但我将根据要求在此处发布示例表架构。

item 表：

+------------+------------+-----------+
| item_id    | name       | attr_id   |
+------------+------------+-----------+
| 1          | BMW 320d   | 20        |
| 1          | BMW 320d   | 21        |
| 2          | BMW 335i   | 23        |
| 2          | BMW 335i   | 34        |
+------------+------------+-----------+

attribute 表：

+---------+-----------------+------------+
| attr_id | value           |  parent_id |
+---------+-----------------+------------+
|   20    | SE              |         21 | 
|   21    | M Sport         |          0 |
|   23    | AC              |         24 |
|   24    | Climate control |          0 |
              ....
|   34    | Leather seats   |          0 |
+---------+-----------------+------------+

返回具有多个属性的项目的简单查询。

SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes 
FROM item GROUP BY item_id HAVING attributes > 1

这给我一个像这样的结果集：

+-----------+------------+
|   item_id | attributes |
+-----------+------------+
|    1      |          2 |
|    2      |          2 |
|    3      |          2 |
        -- etc. --

但是，有一个例外。 attribute 表可以通过表中的父链接保存树结构。对于某些行，parent_id 可以保存另一个属性的 ID。这棵树只有一层。示例：

+---------+-----------------+------------+
| attr_id | value           |  parent_id |
+---------+-----------------+------------+
|   20    | SE              |         21 |
|   21    | M Sport         |          0 |
              ....

我不想想要检索原始查询中的项目，其中对于一对关联属性，它们像属性 20 和 20 一样相关。 21.

我确实想要检索项目，其中：

• 属性
• 对于两个或多个不相关的属性没有父级（例如属性 23 和 34）

所需的示例结果，只需项目 ID：

+------------+
| item_id    |
+------------+
| 2          |
+------------+

如何我加入 items 中的 attributes 并排除这些行？

我是否使用临时表或者可以通过单个查询实现此目的？

谢谢。

Answer 1

以下查询将仅提取唯一的项目和属性对（或其父级，如果有），从而消除重复项（这是根据您的要求，属性只能有一个父级，并且父级没有父级）。

SELECT DISTINCT I.item_id AS iid, A.par_id AS aid
FROM 
    items AS I, 
    (SELECT AA.attr_id, IF(AA.parent_id = 0, AA.attr_id, AA.parent_id) AS par_id 
     FROM attribute AS AA) AS A
WHERE I.attr_id = A.attr_id
ORDER BY I.item_id

因此，使用上述查询作为计数查询的子表将会起作用（与上面的 A 子表使用的方法相同）：

SELECT SUB.iid, COUNT(DISTINCT(SUB.aid)) AS attributes
FROM
    (SELECT DISTINCT I.item_id AS iid, A.par_id AS aid
     FROM 
        items AS I, 
        (SELECT AA.attr_id, IF(AA.parent_id = 0, AA.attr_id, AA.parent_id) AS par_id 
         FROM attribute AS AA) AS A
     WHERE I.attr_id = A.attr_id
     ORDER BY I.item_id) AS SUB
GROUP BY SUB.iid
HAVING attributes > 1

我在示例项目表中添加了 3 行以上，以适应可以链接项目的情况仅针对具有父级的属性，而不是父级本身（即项目 3 -> 23 和 3 -> 20），以及 4 -> 20。 23.

运行上述查询仅列出项目 2 和 3，各有 2 个属性。

Answer 2

您可以通过单个查询来实现：

SELECT
    i.item_id,
    COUNT(DISTINCT(i.attr_id)) AS attributes 
FROM
    items i
INNER JOIN
    attributes a
        ON i.attr_id = a.attr_id
WHERE
    a.parent_id = 0
GROUP BY
    i.item_id
HAVING
    i.labels > 1

Answer 3

好吧，似乎用一个查询是不可能的，因为我们没有什么可以分组的依据，也没有什么可以排序的。
剩下的一件事是进行一些递归调用，但由于 mysql 中没有递归 SQL，或者如果您的属性数据有一个规则，其中对于所有链接的属性 attr_id <<父 ID。

Answer 4

为了简化这一过程，我使用父属性 ID（如果有）更新了 item 中的所有行。

因此，在我的示例 item 表中，更新了属性 ID 后，它看起来像：

+------------+------------+-----------+
| item_id    | name       | attr_id   |
+------------+------------+-----------+
| 1          | BMW 320d   | 21        |
| 1          | BMW 320d   | 21        |
| 2          | BMW 335i   | 23        |
| 2          | BMW 335i   | 34        |
+------------+------------+-----------+

首先，我得到了一个属性关系列表（子到父）：

SELECT a.attr_id, a.parent_id FROM item i JOIN attribute a 
USING (attr_id) WHERE parent_id > 0 GROUP BY a.attr_id

我在代码中循环了这个并更新了中的行引用子属性的item。

$update = array();

foreach ($relations as $child => $parent) {
    if (!isset($update[$parent]))
        $update[$parent] = array();

    $update[$parent][] = $child;
}

循环$update来更新item。完成此操作后，我就可以使用原来的查询：

SELECT item_id, COUNT(DISTINCT(attr_id)) AS attributes 
FROM item GROUP BY item_id HAVING attributes > 1

我无法让一个查询正常工作。