MySQL Join 4 个表只返回 1 行
抱歉我的英语不好，我需要从 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 result
FROM
table_1 AS t1
LEFT JOIN table_2 AS t2
ON t2.col_2 = t1.col_2
LEFT JOIN table_3 AS t3
ON t3.col_1 = t1.id
LEFT JOIN table_4 AS t4
ON t4.id = t3.col_2
WHERE t1.col_2 > 1
ORDER 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 谢谢
ITMISS1回答
-
qq_花开花谢_0
根据您发布的小提琴您所要做的就是将您的查询更改为：-SELECT t1.id, t1.name, t2.role as result_0, GROUP_CONCAT(t4.ct) as resultFROM table_one AS t1LEFT JOIN table_tow AS t2 ON t2.level = t1.level JOIN table_three AS t3 //CHANGED LEFT JOIN TO JOIN ON t3.vid = t1.idLEFT JOIN table_four AS t4 ON t4.id = t3.cidWHERE t1.level > 1group 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
随时随地看视频慕课网APP
PHP