mysql - 如何将多行与来自mysql中多个表的多个连接组合起来

Question

我正在尝试将多行组合成具有多列的单独行，其中数据来自三个表。我遵循了MySQL 数据透视表中的建议，但问题和答案不考虑多个连接。

以下是基础表：

table n
+------+----+------+
| name | id | code |
+------+----+------+
| foo  |  1 | NULL |
| bar  |  2 | z    |
| baz  |  3 | y    |
+------+----+------+

table ac
+------+----+
| code | id |
+------+----+
| h    |  1 |
| i    |  2 |
+------+----+

table c
+-----+------+-------+
| cid | code | desc  |
+-----+------+-------+
|   9 | h    | desch |
|   9 | i    | desci |
|   8 | z    | descz |
|   8 | y    | descy |
+-----+------+-------+

以下是预期结果：

+------+-------+-------+
| name | type8 | type9 |
+------+-------+-------+
| foo  | null  | desch |
| bar  | descz | desci |
| baz  | descy | null  |
+------+-------+-------+

我可以得到非常接近我想要的结果：

select 
n.name,
n.code as type8,
ac.code as type9
from n
left join ac
on ac.id=n.id

但正如预期的那样，这仅产生代码：

+------+-------+-------+
| name | type8 | type9 |
+------+-------+-------+
| foo  | null  | h     |
| bar  | z     | i     |
| baz  | y     | null  |
+------+-------+-------+

我有兴趣用表 c 中的较长描述替换代码。

标签： mysql

Answer 1

第一步是使用mysql 中数据透视表case的技术从同一个表中的多行返回多列数据：

select
n.name,
case when c.cid=8 then c.desc end as type8,
case when c.cid=9 then c.desc end as type9
from n
left join c  
on n.code=c.code 

这对 type8 产生了正确的结果，但对 type9 产生了空值：

+------+-------+-------+
| name | type8 | type9 |
+------+-------+-------+
| foo  | null  | null  |
| bar  | descz | null  |
| baz  | descy | null  |
+------+-------+-------+

下一步是获取 type9 的结果：

select
n.name,
case when c.cid=8 then c.desc end as type8,
case when c.cid=9 then c.desc end as type9
from n
left join ac
on ac.id=n.id
left join c
on c.code=ac.code

这产生：

+------+-------+-------+
| name | type8 | type9 |
+------+-------+-------+
| foo  | null  | desch |
| bar  | null  | desci |
| baz  | null  | null  |
+------+-------+-------+

如果这两个结果结合在一起

select 
n.name,
case when c.cid=8 then c.desc end as type8,
case when c.cid=9 then c.desc end as type9
from n
left join c  
on n.code=c.code

union

select
n.name,
case when c.cid=8 then c.desc end as type8,
case when c.cid=9 then c.desc end as type9
from n
left join ac
on ac.id=n.id
left join c
on c.code=ac.code

这些行仍然需要合并：

+------+-------+-------+
| name | type8 | type9 |
+------+-------+-------+
| bar  | descz | null  |
| baz  | descy | null  |
| foo  | null  | null  |
| foo  | null  | desch |
| bar  | null  | desci |
| baz  | null  | null  |
+------+-------+-------+

最后，我能够通过使用聚合技术从联合查询中删除空值来实现预期的结果：

select 
n.name,
min(type8), min(type9)
from
(select 
n.name,
case when c.cid=8 then c.desc end as type8,
case when c.cid=9 then c.desc end as type9
from n
left join c  
on n.code=c.code

union
select
n.name,
case when c.cid=8 then c.desc end as type8,
case when c.cid=9 then c.desc end as type9
from n
left join ac
on ac.id=n.id
left join c
on c.code=ac.code) as n
group by n.name

产生预期的结果：

+------+-------+-------+
| name | type8 | type9 |
+------+-------+-------+
| foo  | null  | desch |
| bar  | descz | desci |
| baz  | descy | null  |
+------+-------+-------+