mysql 查询问题-一个表多次 join 自己，可以做到每个表返回一行记录么

SELECT *
FROM sys_address three
LEFT JOIN sys_address two ON three.parent_code = two.code
LEFT JOIN sys_address o ON two.parent_code = o.code
WHERE three.CODE = 110101

返回是这样的：

"110101"	"东城区"	"110100"	"3"	"110100"	"市辖区"	"110000"	"2"	"110000"	"北京市"	""	"1"

有办法做到返回这样的结构么：

"110101"	"东城区"	"110100"	"3"
"110100"	"市辖区"	"110000"	"2"
"110000"	"北京市"	""	"1"

22 replies • 2019-06-29 08:56:53 +08:00

Yuicon

Jun 28, 2019

我自己倒是想到一种办法就是有点蛋疼非常长

Yuicon

Jun 28, 2019

```
SELECT three.*
FROM cdshopping.sys_address three
WHERE three.CODE = 110101 UNION

SELECT two.*
FROM cdshopping.sys_address three
LEFT JOIN cdshopping.sys_address two ON three.parent_code = two.code
WHERE three.CODE = 110101 UNION

SELECT o.*
FROM cdshopping.sys_address three
LEFT JOIN cdshopping.sys_address two ON three.parent_code = two.code
LEFT JOIN cdshopping.sys_address o ON two.parent_code = o.code
WHERE three.CODE = 110101
```

Gatsbywl

Jun 28, 2019

SELECT * FROM sys_address
WHERE CODE IN (
-- 3 级地名的 code
110101,
-- 选择上一步的 parent_code
(SELECT three.parent_code
FROM testdb.sys_address three
WHERE three.CODE = 110101),
-- 继续选择上一步的 parent_code,
(SELECT parent_code FROM sys_address
WHERE CODE =
(SELECT three.parent_code
FROM testdb.sys_address three
WHERE three.CODE = 110101))
-- 如果还有需求选择再上一级的'中国'，重复上一个步骤即可
)

Gatsbywl

Jun 28, 2019

我暂时只能想到这种思路，其实就是根据最低级区域的代号往上寻根，所以每次
WHERE code = 上一步的 parent_code

reus

Jun 28, 2019

用 PostgreSQL 或者 MySQL 8 的 recursive CTE

Yuicon

Jun 28, 2019

@Gatsbywl 看来是不存在方便的方案了这种需求估计也少

meetocean

Jun 28, 2019

亲自测试可用：

(
SELECT
three.id as id3, three.title title3
FROM plots three
LEFT JOIN plots two ON three.parent_id = two.id
LEFT JOIN plots one ON two.parent_id = one.id
WHERE three.id = 7
)
UNION
(
SELECT
two.id as id2, two.title title2
FROM plots three
LEFT JOIN plots two ON three.parent_id = two.id
LEFT JOIN plots one ON two.parent_id = one.id
WHERE three.id = 7
)
UNION
(
SELECT
one.id as id1, one.title title1
FROM plots three
LEFT JOIN plots two ON three.parent_id = two.id
LEFT JOIN plots one ON two.parent_id = one.id
WHERE three.id = 7
)

按照这个思路，把 SQL 修改一下即可。

meetocean

Jun 28, 2019

这是硬查询，如果你不需要数据库里的三行，而是三行字符串，那么还有方法，重组字符串。

JQZhang

Jun 28, 2019

还是喜欢 Oracle 的 connect by，你可以搜一下 mysql 仿写的 connect by

Yuicon

Jun 28, 2019

@meetocean 兄弟思路一样啊

Yuicon

Jun 28, 2019

@JQZhang 看了下好像要写函数的不怎么适合

Alexisused

Jun 28, 2019

意义在哪里？

whl619969187

Jun 28, 2019

就是递归查询嘛，mysql 8 好像支持 oracle 支持其他没用过

1ffree

Jun 28, 2019

说明表设计有问题。
要么做个冗余，要么多行取出来内存里做处理

oaix

Jun 28, 2019

code 看起来有编码规范，所以只要 where code in ('110101', '110100', '110000')

Yuicon

Jun 28, 2019

@Alexisused ......
因为这实际是三条记录我当然希望返回的三个对象

Yuicon

Jun 28, 2019

@1ffree 怪过去也没用人都走了现在写的是我才是现实

meetocean

Jun 28, 2019

@oaix
从楼主的题目，可推断数据表是层级表，并且只有三级，分别是省、市、区。
用户给定的查询条件就是给定叶子结点（没有子节点的节点）的一个具体值“ 110000 ”。
根据这个条件得到上级（市）与上上级（省）数据。

Yuicon

Jun 28, 2019

@whl619969187 比起引入存储过程或者自定义函数我宁愿查三次。。。

Yuicon

Jun 28, 2019

@meetocean 我觉得他是个天才把复杂度转移到业务层去了可惜这种药一开始就约定好

saulshao

Jun 28, 2019

这个建议是要多次查询这个表，不建议完全用 SQL.

Takamine

Jun 29, 2019

第一，我不会这么写；
第二，我不会这么写。:doge:
第三，作为 CRUDboy 我会把这个放到业务层。