MySQL 复杂查询未产生正确结果

Question

我有两个表：车辆（Id，VIN）和图像（Id，VehicleId，名称，默认）。 我需要选择要在表格中显示的车辆 VIN 及其默认图片。 我遇到的问题是，如果未设置默认图片，我仍然想选择要显示的图像（如果存在）。 如果不存在图像，则车辆信息显然仍必须显示。 这是我到目前为止所得到的：

SELECT
    Vehicles.Id, Vehicles.VIN, Images.Name AS Image,
    (SELECT COUNT(*) FROM Images WHERE VehicleId = Vehicles.Id) AS ImageCount
FROM
    Vehicles
LEFT JOIN
    Images ON Images.VehicleId = Vehicles.Id
WHERE
    Images.Default = 1

此语句将显示具有默认图像的车辆，但如果未设置默认图像，则不会显示任何内容。

为了更好地描述我的问题，这里有一些测试数据：

VEHICLES:
ID VIN
1  12341234123412341
2  23452345234523452
3  34534534534534534

IMAGES:
ID VEHICLEID NAME DEFAULT
1  1         a    1
2  1         b    0
3  2         c    0
4  2         d    0

尽管车辆 2 没有默认值，但我希望它选择要显示的图像。 此外，车辆 3 根本没有图像，但我仍然需要它在没有图像的情况下显示在表格中。 车辆 1 将显示其默认图像，因为它已设置。 我希望这能解决问题。

Answer 1

好的，设置如下：

mysql> create table VEHICLES ( ID INT PRIMARY KEY, VIN CHAR( 17 ) );
mysql> INSERT INTO VEHICLES ( ID, VIN ) VALUES( 1, '12341234123412341' );
mysql> INSERT INTO VEHICLES ( ID, VIN ) VALUES( 2, '23452345234523452' );
mysql> INSERT INTO VEHICLES ( ID, VIN ) VALUES( 3, '34534534534534534' );

注意，我必须将 IMAGES 表中的 DEFAULT 列重命名为 DEF：

mysql> CREATE TABLE IMAGES ( ID INT PRIMARY KEY, VEHICLEID INT, NAME VARCHAR(20), DEF INT );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 1, 1, 'a', 1 );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 2, 1, 'b', 0 );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 3, 2, 'c', 0 );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 4, 2, 'd', 0 );

这是解决方案。

首先，我们需要一个查询，为每辆车获取一个图像行，如果有则选择默认图像行。

为此，我们按 DEF 降序对图像进行排序（因此 1 位于顶部），然后按 VEHICLEID 分组以确保每辆车只有一行。

mysql> SELECT * FROM ( SELECT * FROM IMAGES ORDER BY DEF DESC ) sortedimages GROUP BY VEHICLEID;
+----+-----------+------+------+
| ID | VEHICLEID | NAME | DEF  |
+----+-----------+------+------+
|  1 |         1 | a    |    1 | 
|  3 |         2 | c    |    0 | 
+----+-----------+------+------+

现在，我们从 VEHICLES 表中进行选择，并使用 LEFT OUTER JOIN 进行上述查询，以确保我们始终为每辆车获取一行：

mysql> SELECT * FROM VEHICLES LEFT OUTER JOIN ( SELECT * FROM ( SELECT * FROM IMAGES ORDER BY DEF DESC ) sortedimages GROUP BY VEHICLEID ) defaultimages ON VEHICLES.ID = defaultimages.VEHICLEID;
+----+-------------------+------+-----------+------+------+
| ID | VIN               | ID   | VEHICLEID | NAME | DEF  |
+----+-------------------+------+-----------+------+------+
|  1 | 12341234123412341 |    1 |         1 | a    |    1 | 
|  2 | 23452345234523452 |    3 |         2 | c    |    0 | 
|  3 | 34534534534534534 | NULL |      NULL | NULL | NULL | 
+----+-------------------+------+-----------+------+------+

Answer 2

SELECT
    Vehicles.Id, Vehicles.VIN, Images.Name AS Image,
    (SELECT COUNT(*) FROM Images WHERE VehicleId = Vehicles.Id) AS ImageCount
FROM
    Vehicles
LEFT JOIN
    Images ON Images.VehicleId = Vehicles.Id
WHERE
    1

应该可以解决这个问题，因为您受到默认设置为 1 的事实的限制。我认为这回答了您的问题，但可能需要针对您想要的实际结果进行细化 - （您是否想要获取图像的名称， 或者是什么？）