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 条回复 • 2019-06-29 08:56:53 +08:00

Yuicon

2019-06-28 11:21:58 +08:00

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

Yuicon

2019-06-28 11:22:23 +08:00

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

2019-06-28 12:22:17 +08:00

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

2019-06-28 12:26:07 +08:00

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

reus

2019-06-28 12:26:21 +08:00

用 PostgreSQL 或者 MySQL 8 的 recursive CTE

Yuicon

2019-06-28 12:41:34 +08:00

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

meetocean

2019-06-28 13:01:33 +08:00

亲自测试可用：

(
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

2019-06-28 13:11:07 +08:00

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

JQZhang

2019-06-28 13:20:32 +08:00

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

Yuicon

2019-06-28 13:38:39 +08:00

@meetocean 兄弟思路一样啊

Yuicon

2019-06-28 13:40:25 +08:00

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

Alexisused

2019-06-28 13:53:13 +08:00

意义在哪里？

whl619969187

2019-06-28 16:22:18 +08:00

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

1ffree

2019-06-28 16:32:07 +08:00

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

oaix

2019-06-28 16:49:54 +08:00

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

Yuicon

2019-06-28 17:08:41 +08:00

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

Yuicon

2019-06-28 17:09:42 +08:00

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

meetocean

2019-06-28 17:10:00 +08:00

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

Yuicon

2019-06-28 17:12:33 +08:00

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

Yuicon

2019-06-28 17:14:50 +08:00

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

saulshao

2019-06-28 17:55:18 +08:00

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

Takamine

2019-06-29 08:56:53 +08:00

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