MySQL - 使用不匹配行数的多个子查询的结果

问题描述：

我不知道这是否可能，但我试图在How to specify the parent query field from within a subquery in mySQL?给出的建议下使用多个子查询中的MySQL SELECT查询的值。每个子查询中都有不同数量的结果，我怀疑这是为什么我无法实现这一功能。MySQL - 使用不匹配行数的多个子查询的结果

我的数据库比上面的帖子更复杂。我负责的5桌，内容如下：

substances - 化学物质清单，所有这些都有一个唯一的ID（substances.id）
ecs - EC号*值。
cas - CAS编号*值。
ecs_substances - 映射substances.id至ecs.id。一个substances.id可以被映射到一个，或多于一个的ecs.id
cas_substances - 映射substances.id到cas.id。一个substances.id可以被映射到一个，或多于一个的cas.id

*这些是用于化学品的分类条件。

的数据可能会如何看待1种物质的例子：

substances.id | substances.name 
-------------- | ---------------- 
1    | FooBar

分配到多个CAS编号的一个例子（同样的原则也适用于欧盟，但我只是用CAS为简洁起见）：

cas_substances.id | substance_id 
-------------------------------- 
997    | 1 
----------------- | ------------ 
23423    | 1 
--------------------------------

然后是CAS数字本身：

cas.id | cas.value 
------ | --------- 
997 | ABC-123 
------ | --------- 
23423 | XYZ-876 
------------------

期望的结果是我想获得每个substances.id, substances.name的列表以及CAS/EC编号的相应列表，理想地用换行符格式化，例如，

substances.id | substances.name | cas_values   | 
-------------- | --------------- | ------------------ | 
1    | FooBar   | ABC-123 "\n" XYZ-876

我明白，逻辑如下：

SELECT * FROM substances
SELECT * FROM cas_substances WHERE cas_substances.substance_id =从1（substances.id）从2个结果
SELECT cas.value FROM cas WHERE cas.cas.id =个结果（cas_substances.cas_id）
格式处理可能（或可能不是）多行cas_substances行的事实 - 如果存在记录之间的换行符。

我不明白如何编写获取的结果来自1/2。

它甚至有可能在MySQL中做到这一点？我正在构建的应用程序是用PHP编写的，但是我认为只使用SQL会比在PHP中使用多个子查询更快（尽管使用PHP编写起来要容易得多）。

检查一个GROUP_CONCAT（），它应该是罚款与SQL –

答

您可以将这三个表加在一起，然后使用group by和group_concat来实现。

select 
    `sub`.*, 
    group_concat(cas.`value` separator ' "\n" ') as cas_values 
from substances `sub` 
left join cas_substances cs on `sub`.id = cs.substance_id 
left join cas on cas.id = cs.cas_id 
group by `sub`.id

A demo这里。

注意：GROUP_CONCAT只适用于mysql。

非常感谢你，这个工程。是否可以给'group_concat（）'一个别名，因为在返回的数据中，列名是'group_concat（cas.'value' separator'“\ n”'）'。我也需要为EC编号做同样的事情，所以可以用col_quot和'ec_values'来处理，以便稍后参考。 – Andy

@安迪别名是可能的。 – Blank

答

接受的答案的作品，但我已经扩大这包括所有5个表：

select 
`sub`.*, 
group_concat(cas.`value` separator ' "\n" ') as cas_values, 
group_concat(ecs.`value` separator ' "\n" ') as ecs_values 
from substances `sub` 
left join cas_substances cs on `sub`.id = cs.substance_id 
left join cas on cas.id = cs.cas_id 
left join ecs_substances ec on `sub`.id = ec.substance_id 
left join ecs on ecs.id = ec.ec_id 
group by `sub`.id

MySQL - 使用不匹配行数的多个子查询的结果

相关推荐