代码之家 › 专栏 › 技术社区 › Greg K

如何排除SQL树中匹配联接所在的行

join tree mysql sql

Greg K · 技术社区 · 14 年前

更新: 在花了几个小时来研究这个问题之后,我们使用了一个多查询解决方案,并使用了一个只包含父属性的表来确定哪些项需要更新。

对不起,标题不好,我想不出如何简明地描述这个问题。

我有一组项应该与一个属性有一对一的关系。

我有一个查询来返回那些数据错误且此关系已断开(1对多)的行。我正在收集这些行来修复它们并恢复这种1对1的关系。

这是对我实际问题的理论简化,但我将按照请求在这里发布示例表模式。

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. --

不过,也有例外。这个 属性 表可以通过表中的父链接保存树结构。对于某些行, parent_id 可以保存另一个属性的ID。这棵树只有一层。例子:

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

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

我做要检索以下位置的项:

属性没有父级
对于两个或多个属性,它们不相关(例如属性23&34)

所需的示例结果,仅项目ID:

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

我怎么能加入反对 attributes 从 items 排除这些行?

我是使用临时表还是可以通过单个查询实现?

谢谢。

4 回复 | 直到 14 年前

Sunny Milenov 14 年前

以下查询将只提取唯一的项和属性对(或其父项,如果有的话),从而消除重复项(这是根据您的请求,即一个属性只能有一个父项,而父项没有父项)。

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

所以,使用上面的查询作为计数查询的子表将起作用(和我在上面的子表中使用的方法相同):

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->23)。

运行上述查询时,仅列出项2和项3,每个项有2个属性。

St.Woland 14 年前

您可以通过一个查询实现这一点:

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

Patrick 14 年前

好吧,似乎一个查询是不可能的,因为我们没有可分组的内容,或者没有可排序的内容。剩下的一件事是执行一些递归调用,但由于mysql中没有递归sql,或者如果属性数据有一个规则where for all linked attr_id<parent_id。

Greg K 14 年前

为了简化这一点,我更新了 item 使用父属性id,其中一个可用。

在我的例子中 项目 表,更新了属性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

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

$update = array();

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

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

环绕 $update 更新 项目 . 完成此操作后,我可以使用原始查询:

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

我一个问题都做不到。