mysql - 加入自我表并在新行中获取值

Question

我想知道是否有办法加入一个自我表，然后在新行中获取这些值。

原始表与此类似：

| id | slug    | parent_id |
|----|---------|-----------|
| 1  | one     | NULL      |
| 2  | two     | 1         | 

我从可能包含 N 个 JOIN 语句的代码中动态生成此查询：

SELECT * FROM `permalinks` 
  INNER JOIN `permalinks` AS `permalinks1` 
    ON `permalinks`.`id` = `permalinks1`.`parent_id` 
        AND `permalinks1`.`slug` = 'two' 
  WHERE `permalinks`.`slug` = 'one'

这会产生：

| id | slug    | id | slug   |  
|----|---------|----|--------|
| 1  | one     | 2  | two    |

但是我想得到：

| id | slug    |
|----|---------|
| 1  | one     |
| 2  | two     |

是否可以将所有新的连接值作为新行？

标签： mysqlsqljoininner-join

Answer 1

可以通过使用相同的查询两次并稍作调整并在联合后生成行来完成

SELECT perm.Id, perm.slug FROM permalinks as perm
  INNER JOIN permalinks AS permalinks1 
    ON perm.id = permalinks1.parent_id 
        AND permalinks1.slug = 'two' 
  WHERE perm.slug = 'one'
  union
SELECT permalinks1.Id, permalinks1.slug FROM permalinks as perm
  INNER JOIN permalinks AS permalinks1 
    ON perm.id = permalinks1.parent_id 
        AND permalinks1.slug = 'two' 
  WHERE perm.slug = 'one';