我创建了此查询,即使在车辆表上创建了 0 辆汽车,我也无法显示所有品牌汽车。
SELECT BRAND_CAR_NAME, COUNT(*)
FROM brand b, vehicule v, model mo
WHERE v.ID_MODEL = mo.ID_MODEL
AND mo.ID_BRAND = b.ID_BRAND
GROUP BY BRAND_CAR_NAME
它向我展示了什么:
Brand 1 : 1
Brand 3 : 1
Brand 4 : 1
Brand 6 : 1
我想看看:
Brand 1 : 1
Brand 3 : 1
Brand 4 : 1
Brand 6 : 1
brand 2 : 0
brand 5 : 0
brand 7 : 0
SELECT NOM_MARQUE, IFNULL(v.ID_VOITURE)
FROM marque m, voiture v, model mo
WHERE v.ID_MODEL = mo.ID_MODEL
AND mo.ID_MARQUE = m.ID_MARQUE
GROUP BY NOM_MARQUE
使用 Left Join
获取结果,请尝试以下查询
SELECT b.BRAND_CAR_NAME, COUNT(*)
FROM brand b
LEFT JOIN model mo ON b.ID_BRAND = mo.ID_BRAND
INNER JOIN vehicule v ON mo.ID_MODEL = v.ID_MODEL
GROUP BY b.BRAND_CAR_NAME;
您可以使用
LEFT JOIN
,尤其是在vehicule
表之前SUM( IFNULL(SIGN(v.ID_MODEL),0) )
(我认为最好在表之前使用LEFT JOIN
model
,再次model
表缺少记录(
SELECT BRAND_CAR_NAME, SUM( IFNULL(SIGN(v.ID_MODEL),0) ) as BRAND_COUNT
FROM brand b
LEFT JOIN model mo ON mo.ID_BRAND = b.ID_BRAND
LEFT JOIN vehicule v ON v.ID_MODEL = mo.ID_MODEL
GROUP BY BRAND_CAR_NAME;
演示