连接self表并获取新行中的值

l3zydbqr  于 2021-06-19  发布在  Mysql
关注(0)|答案(1)|浏览(444)

我想知道是否有方法连接一个self表,然后在新行中获取这些值。
原始表类似于:

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

是否可以将所有新的联接值作为新行获取?

ldxq2e6h

ldxq2e6h1#

这可以通过使用同一查询两次来完成,只需稍加调整,然后在并集后生成行

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';

相关问题