抱歉我的英语不好,我需要从 mysql 4 表中获取数据,但它只返回 1 行。表格1表 1 中的 col_2 包含数字数据作为表 2 col_2 的参考+----+-------+---------+| id | col_1 | col_2 |+----+-------+---------+| 1 | A | 4 || 2 | B | 5 || 3 | C | 6 |+----+-------+---------+表 2+----+-------+---------+| id | col_1 | col_2 |+----+-------+---------+| 1 | A | 4 || 2 | B | 5 || 3 | C | 6 || 4 | C | 7 || 5 | C | 8 |+----+-------+---------+表3表 3 col_1 具有表 1 id 的数值,但大多数主题将是相同的 id,col_2 具有表 4 的 id 数值+----+-------+---------+| id | col_1 | col_2 |+----+-------+---------+| 1 | 1 | 1 || 2 | 1 | 2 || 3 | 1 | 3 || 4 | 2 | 4 || 5 | 2 | 5 || 6 | 4 | 8 || 7 | 4 | 1 || 8 | 5 | 2 || 9 | 5 | 5 || 10 | 5 | 8 || 11 | 5 | 9 || 12 | 5 | 10 |+----+-------+---------+表 4+----+-------+| id | col_1 |+----+-------+| 1 | A || 2 | B || 3 | C |+----+-------+我运行以获取数据的查询。SELECT t1.id, t1.col_1, t2.col_1 as result_0, GROUP_CONCAT(t4.col_1) as resultFROM table_1 AS t1LEFT JOIN table_2 AS t2 ON t2.col_2 = t1.col_2LEFT JOIN table_3 AS t3 ON t3.col_1 = t1.idLEFT JOIN table_4 AS t4 ON t4.id = t3.col_2WHERE t1.col_2 > 1ORDER BY t1.id DESC如您所见,表 3 具有与表 1 和表 4 不同的 id,这两个表都有值,所以我希望查询返回如下所示。这意味着我希望表 4 中的值用逗号分隔。+----+-----------+-----------+-----------+| id | result_1 | result_2 | result_3 |+----+-----------+-----------+-----------+| 1 | A | A | A,B,C || 2 | B | B | A,B || 3 | C | C | C,B | +----+-----------+-----------+-----------+更新: 这里要求的是查询的小提琴。SQL Fiddle http://sqlfiddle.com/#!9/3af0af/1 谢谢
1 回答
![?](http://img1.sycdn.imooc.com/54dc06a60001ef0401000100-100-100.jpg)
qq_花开花谢_0
TA贡献1835条经验 获得超7个赞
根据您发布的小提琴
您所要做的就是将您的查询更改为:-
SELECT
t1.id,
t1.name,
t2.role as result_0,
GROUP_CONCAT(t4.ct) as result
FROM
table_one AS t1
LEFT JOIN table_tow AS t2
ON t2.level = t1.level
JOIN table_three AS t3 //CHANGED LEFT JOIN TO JOIN
ON t3.vid = t1.id
LEFT JOIN table_four AS t4
ON t4.id = t3.cid
WHERE t1.level > 1
group by t1.id ORDER BY t1.id ASC //ADDED group by t1.id AND CHANGED ORDER BY t1.id DESC TO ORDER BY t1.id ASC
- 1 回答
- 0 关注
- 96 浏览
添加回答
举报
0/150
提交
取消