mysql多对多关系作为列
mysql多对多关系作为列
提问于 2018-05-17 11:45:26
我有以下结构：
case:
id int
...
behaviours:
id int,
label varchar
case_to_behaviour:
case_id,
behaviour_id行为是一个集合列表，它不会被用户动态添加、编辑或删除。
有许多这样的多对多关系需要我报告，但正确地处理这一个将给我一个模板来处理其余的关系。
到目前为止，因为只有8个列出的‘行为’，我有下面的查询，它让我得到我想要的，我多么希望有一个更好或更有效的方式来查询它。
数据需要像这样呈现：
id | behaviour_1 | behaviour_2 | behaviour_3 | behaviour_4 | behaviour_5 ....
----------------------------------------------------------------------------
1 | true | true | false | false | true脚本，我目前用来得到他的是如下所示，但只能工作到9个可能的项目。
SELECT
case.id,
IF(csb.behaviours like '%1%', true, false) as `behaviour_1`,
IF(csb.behaviours like '%2%', true, false) as `behaviour_2`,
IF(csb.behaviours like '%3%', true, false) as `behaviour_3`,
IF(csb.behaviours like '%4%', true, false) as `behaviour_4`,
IF(csb.behaviours like '%5%', true, false) as `behaviour_5`,
IF(csb.behaviours like '%6%', true, false) as `behaviour_6`,
IF(csb.behaviours like '%7%', true, false) as `behaviour_7`,
IF(csb.behaviours like '%8%', true, false) as `behaviour_8`
from case c
left join
(select group_concat(behaviour_id) as behaviours, case_id from case_to_behavior group by case_id) csb on csb.case_id=c.id
group by c.id;编辑:我已经尝试了一个类似的查询，就像下面的demonstraight，它只适用于第一个连接，这是有意义的。我只是不知道如何以我想要的格式获得数据。
SELECT
case.id,
IF(csb.behaviour_id = 1, true, false) as `behaviour_1`,
IF(csb.behaviour_id = 2, true, false) as `behaviour_2`,
IF(csb.behaviour_id = 3, true, false) as `behaviour_3`,
IF(csb.behaviour_id = 4, true, false) as `behaviour_4`,
IF(csb.behaviour_id = 5, true, false) as `behaviour_5`,
IF(csb.behaviour_id = 6, true, false) as `behaviour_6`,
IF(csb.behaviour_id = 7, true, false) as `behaviour_7`,
IF(csb.behaviour_id = 8, true, false) as `behaviour_8`,
from case c
left join case_to_behavior csb on csb.case_id=c.id
group by c.id; 回答 1
Stack Overflow用户
发布于 2018-05-18 07:15:06
我已经算出来了：
SELECT
case.id,
sum(IF(csb.behaviour_id = 1, true, false)) as `behaviour_1`,
sum(IF(csb.behaviour_id = 2, true, false)) as `behaviour_2`,
sum(IF(csb.behaviour_id = 3, true, false)) as `behaviour_3`,
sum(IF(csb.behaviour_id = 4, true, false)) as `behaviour_4`,
sum(IF(csb.behaviour_id = 5, true, false)) as `behaviour_5`,
sum(IF(csb.behaviour_id = 6, true, false)) as `behaviour_6`,
sum(IF(csb.behaviour_id = 7, true, false)) as `behaviour_7`,
sum(IF(csb.behaviour_id = 8, true, false)) as `behaviour_8`,
from case c
left join case_to_behavior csb on csb.case_id=c.id
group by c.id; 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50383021
复制相关文章
相似问题
腾讯云开发者
