假设我有一个表:"汽车",有 3 个字段:id、品牌、成本。
还有第二个表:"模型",有 3 个字段:id、品牌model_name。
每个"汽车"行可以有多个相关的"模型"行。
是否可以执行输出如下所示的sql选择?
编辑:我使用PHP进行数据库查询
array(
[0] => array(
[id] => 1
[brand] => mercedes
[cost] => 1000
[models] => array(
[0] => array(
[id] => 1
[brand] => mercedes
[model_name] => slk
)
[1] => array(
[id] => 2
[brand] => mercedes
[model_name] => clk
)
[2] => array(
[id] => 3
[brand] => mercedes
[model_name] => whatever
)
)
)
)
您需要
向模型表添加一个外键关系,例如car_id
. 然后:
SELECT * FROM cars JOIN models ON car_id = models.id;
这将输出类似于您正在寻找的内容。
假设您使用的是 PHP,则使用输出:
$query= "SELECT * FROM cars JOIN models ON car_id = models.id";
$r= @mysqli_query($dbc, $query);
while ($row= mysqli_fetch_array($r, MYSQLI_ASSOC)) {
$carstuff['id']=$row[id];
$carstuff['brand']=$row[brand];
$carstuff['cost']=$row[cost];
$carstuff[$row['models']][]=$row['model_name'];
}
var_dump($carstuff);
请注意,id、品牌和成本会被反复覆盖,但这没关系,因为它们被相同的信息覆盖。 我不太确定代码的清洁度,但这是基本思想。
试试这个:
查询:
SELECT c.ID, c.brand,c.cost, GROUP_CONCAT(model_name SEPARATOR '","') as models
, GROUP_CONCAT(m.ID SEPARATOR ',') as MID
, GROUP_CONCAT(m.brand SEPARATOR '","') as mbrand
FROM cars c
LEFT OUTER JOIN model m
ON m.brand = c.brand
GROUP BY brand;
输出:
ID BRAND COST MODELS MID MBRAND
1 audi 1000 m11","m22 4,5 audi","audi
1 mercedes 1200 m1","m2","m3 1,2,3 mercedes","mercedes","mercedes
现在在你的php代码中,你可以处理MODEL,MID和MBrand(通过使用爆炸)
$modelArray = explode(" ,", $row["MODELS");
SQLFIDDLE