我有三个表,一个用于企业列表,一个用于dining_types,一个用于dining_listing_types。
business_listings:
+--------+------------------+------------------+
| id | business_type | business_name |
+--------+------------------+------------------+
| 1 | 1 | china house |
+--------+------------------+------------------+
dining_listing_types:
+--------+------------------+------------------+
| id | listing_id | type_id |
+--------+------------------+------------------+
| 1 | 1 | 1 |
+--------+------------------+------------------+
| 2 | 1 | 3 |
+--------+------------------+------------------+
dining_types:
+--------+------------------+
| id | type |
+--------+------------------+
| 1 | Asian |
+--------+------------------+
| 2 | Italian |
+--------+------------------+
| 3 | Chinese |
+--------+------------------+
正如您所看到的,一个列表可以有多种类型,dining_listing_types表充当“中间人”表。
但是,当我尝试在查询中使用连接时,我返回了两条记录
SELECT bl.id AS id, bl.business_name AS name, dt.type AS type
FROM business_listings bl
JOIN dining_listing_types dlt ON bl.id = dlt.listing_id
JOIN dining_types dt ON dlt.type_id = dt.id
结果:
array(2) { [0]=> object(stdClass)#7 (3) { ["id"]=> string(1) "1" ["name"]=>
string(23) "China House" ["type"]=> string(5) "Asian" } [1]=>
object(stdClass)#8 (3) { ["id"]=> string(1) "1" ["name"]=> string(23) "China
House" ["type"]=> string(18) "Chinese" } }
我不想那样做。我尝试了一个GROUP BY bl.id
,我得到了一条记录,但另一种类型丢失了:
SELECT bl.id AS id, bl.business_name AS name, dt.type AS type
FROM business_listings bl
JOIN dining_listing_types dlt ON bl.id = dlt.listing_id
JOIN dining_types dt ON dlt.type_id = dt.id
GROUP BY bl.id
结果:
array(1) { [0]=> object(stdClass)#7 (3) { ["id"]=> string(1) "1" ["name"]=>
string(23) "China House" ["type"]=> string(5) "Asian" } }
我想要的结果是:
array(1) { [0]=> object(stdClass)#7 (3) { ["id"]=> string(1) "1" ["name"]=>
string(23) "China House" ["type"]=> array(2) ["Asian", "Chinese"] } }
发布于 2019-03-05 06:16:19
正如@rickdenhaan评论的那样,GROUP_CONCAT()
似乎非常接近你正在寻找的东西。这不会生成type
数组,而是生成一个逗号分隔的列表。另一个奇妙的选择是生成一个JSON数组,但我不确定这在这里是否真的有用。
为了按照您的预期工作,需要将business_name
添加到非聚集列的列表中:
SELECT bl.id AS id, bl.business_name AS name, GROUP_CONCAT(dt.type) AS types
FROM business_listings bl
INNER JOIN dining_listing_types dlt ON bl.id = dlt.listing_id
INNER JOIN dining_types dt ON dlt.type_id = dt.id
GROUP BY bl.id, bl.business_name
注意:正如@spencer7593评论的那样,如果我们需要返回没有相关LEFT JOIN
的INNER JOIN
,那么使用business_listings
s而不是dining_types
s可能是一个好主意。
发布于 2019-03-05 07:33:29
根据您的输出数据,我假设您使用的是PHP。如果您使用的是mysqli
类(带有一个连接$conn
),那么您可以使用如下代码(按照@rickdenhaan的建议使用GROUP_CONCAT
)来实现您想要的功能:
$sql = 'SELECT bl.id AS id, bl.business_name AS name, GROUP_CONCAT(dt.type) AS type
FROM business_listings bl
LEFT JOIN dining_listing_types dlt ON bl.id = dlt.listing_id
LEFT JOIN dining_types dt ON dlt.type_id = dt.id
GROUP BY bl.id, bl.business_name';
$result = $conn->query($sql) or die($conn->error);
while ($obj = $result->fetch_object()) {
$obj->type = explode(',', $obj->type);
// do something with $obj
}
https://stackoverflow.com/questions/54992204
复制相似问题