mysql - 多个属性的sql自连接

Question

我有一个(旧)表，其中包含列:

bug_num   build_id    closed_to
1         3            NULL
2         4            NULL
3         NULL         1
4         3            NULL
5         NULL         2

我想编写一个查询，从中选择特定构建中的所有错误，以及已关闭该构建中的错误的所有错误。因此，如果我想为构建 3 执行此操作，它将包括 #s 1 和 4(因为它们在构建 3 中)以及 3，因为它已解决构建 3 (1) 中的错误。

我以为我很接近:

SELECT stat.bug_num, 
       stat.build_id 
FROM   bug_status stat 
       JOIN bug_status stat2 
         ON stat2.closed_to = stat.bug_num 
WHERE  stat.build_id = 3; 

...但它似乎并没有给我想要的结果。感谢您的帮助!

Answer 1

SELECT stat.bug_num,
       stat.build_id
  FROM bug_status  stat
 WHERE stat.build_id = 3
    OR stat.closed_to IN
        ( SELECT stat2.bug_num
            FROM bug_status  stat2
           WHERE stat2.build_id = 3
        )
;

(也可以通过 JOIN 或 JOIN 和 UNION 来实现，但我相信以上是最直观的方式。)

---

编辑添加:这是证明上述内容的 MySQL 脚本:

mysql> create table bug_status
    -> (bug_num numeric, build_id numeric, closed_to numeric);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bug_status values (1, 3, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug_status values (2, 4, null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into bug_status values (3, null, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug_status values (4, 3, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug_status values (5, null, 2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT stat.bug_num,
    ->        stat.build_id
    ->   FROM bug_status  stat
    ->  WHERE stat.build_id = 3
    ->     OR stat.closed_to IN
    ->         ( SELECT stat2.bug_num
    ->             FROM bug_status  stat2
    ->            WHERE stat2.build_id = 3
    ->         )
    -> ;
+---------+----------+
| bug_num | build_id |
+---------+----------+
|       1 |        3 |
|       3 |     NULL |
|       4 |        3 |
+---------+----------+
3 rows in set (0.00 sec)

---

编辑添加，因为 IN (...) 方法似乎在 OP 版本的 MySQL 中不起作用:这是一个替代查询，它给出同样的结果:

SELECT stat.bug_num,
       stat.build_id
  FROM bug_status  stat
  LEFT
 OUTER
  JOIN bug_status  stat2
    ON stat.closed_to = stat2.bug_num
 WHERE stat.build_id = 3
    OR stat2.build_id = 3
;