代码之家 › 专栏 › 技术社区 › Michal M

数据库返回多维结果

multidimensional-array join database mysql sql

Michal M · 技术社区 · 16 年前

这是我们的后续问题 my previous one .
情况:

Table 1:
+--------------------+--------------------+
|               v_id |             v_name |
+--------------------+--------------------+
|                  1 |            v_name1 |
+--------------------+--------------------+
| etc...

Table 2:
+--------------------+--------------------+
|               a_id |             a_name |
+--------------------+--------------------+
|                  1 |            a_name1 |
+--------------------+--------------------+
| etc...

Table 3:
+--------------------+--------------------+
|               v_id |               a_id |
+--------------------+--------------------+
|                  1 |                  1 |
+--------------------+--------------------+
|                  1 |                  2 |
+--------------------+--------------------+
|                  1 |                  3 |
+--------------------+--------------------+

SELECT t1.*, t2.a_name
FROM `table1` t1
LEFT JOIN `table_3` t3 ON t3.v_id = t1.v_id
LEFT JOIN `table_2` t2 ON t2.a_id = t3.a_id
WHERE t1.id = 1;

对于给定的表,此查询的结果将是3行,每行具有来自的相同值 Table 1 ,只是不同而已 a_name 从…起 Table 3 .
名字 数组(在本例中为3个单元格)?我认为这是不可能的。如果不是,我将如何构建查询,使其只返回一行,并且 名字

编辑如果我要在PHP中获得一个结果,我希望得到如下结果:

$result = array(
    'v_id'    => 1,
    'a_name'  => array('a_name1', 'a_name2', 'a_name3')
);

或

$result = array(
    'v_id'    => 1,
    'a_name'  =>'a_name1, a_name2, a_name3'), # assuming I used ', ' as the glue string
);

2 回复 | 直到 9 年前

Sean Vieira 16 年前

我相信你正在寻找 GROUP_CONCAT 功能。

在您的查询中,它将如下所示:

SELECT t1.*, GROUP_CONCAT(t2.a_name SEPARATOR ',')
FROM `table1` t1
LEFT JOIN `table_3` t3 ON t3.v_id = t1.v_id
LEFT JOIN `table_2` t2 ON t2.a_id = t3.a_id
WHERE t1.id = 1
GROUP BY [list of t1 columns here] 
-- update thanks to OMG Ponies.

OMG Ponies 16 年前

使用:

    SELECT t1.*, 
           GROUP_CONCAT(DISTINCT t2.a_name SEPARATOR ',')
     FROM `table1` t1
LEFT JOIN `table_3` t3 ON t3.v_id = t1.v_id
LEFT JOIN `table_2` t2 ON t2.a_id = t3.a_id
   WHERE t1.id = 1
 GROUP BY [list of t1 columns here]

Sean Viera 的答案与您指出的一样有效,因为 WHERE MySQL allows for columns to be omitted from the GROUP BY

我在列表中添加了distinct GROUP_CONCAT