时间戳

首页 > 解决方案 > MySQL Join 4 个表只返回 1 行

php - 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 谢谢

标签： phpmysql

解决方案

根据您发布的小提琴

您所要做的就是将您的查询更改为：-

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

推荐阅读