MySQL 复杂 JOIN 问题 - 无法获得正确的结果

发布于 2024-08-02 05:56:48 字数 868 浏览 5 评论 0原文

我有两个表:车辆(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 将显示其默认图像,因为它已设置。我希望这能解决问题。

I have two table: Vehicles(Id, VIN) and Images(Id, VehicleId, Name, Default). I need to select the vehicles VIN and its default picture to display in a table. The problem I am having is that if a default picture is not set I still would like to select an image to display if it exists. If no images exist the vehicle information obviously must also still display. Here is what I have so far:

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

This statement will display vehicles which have a default image but will not display anything if no default is set. What do I need to change?

EDIT*

To describe my problem better here is some test data:

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

Even though vehicle 2 has no default I want it to select an image to display. Also vehicle 3 has no images at all but I still need it to show up in the table with no image. Vehicle 1 will display its default image because it is set. I hope this clears things up.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(5

池木 2024-08-09 05:56:48

WHERE Images.Default = 1 OR Images.Default IS NULL

where 子句将左联接转变为内联接,因为它需要匹配的记录来满足条件。

WHERE Images.Default = 1 OR Images.Default IS NULL

The where clause is turning the left join into an inner join, since it requires a matching record to satisfy the criteria.

活雷疯 2024-08-09 05:56:48

好的,设置如下:

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 | 
+----+-------------------+------+-----------+------+------+

OK, here's the setup:

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' );

Note I had to rename the column DEFAULT to DEF in the IMAGES table:

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 );

And here's the solution.

First we need a query that gets one image row per vehicle, choosing the default one if there is one.

We do this by sorting the images in descending order of DEF (so the 1s are at the top), and then grouping by VEHICLEID to make sure there is only one row per vehicle.

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 | 
+----+-----------+------+------+

Now we select from the VEHICLES table, and LEFT OUTER JOIN to the above query to make sure we always get one row per vehicle:

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 | 
+----+-------------------+------+-----------+------+------+
木槿暧夏七纪年 2024-08-09 05:56:48
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 OR Images.Name IS NULL

这假设所有图像都有一个名称。原始查询的问题在于,要求“Images.Default = 1”意味着查询必须匹配图像。允许 Images.Id 为 NULL 还会捕获没有图像匹配且左连接填充空值的情况。

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 OR Images.Name IS NULL

This assumes that all Images have a Name. The problem with your original query is that requiring "Images.Default = 1" implies that the query has to have matched an Image. Allowing Images.Id to be NULL will also catch the case where no image was matched and the left join filled in null values.

我ぃ本無心為│何有愛 2024-08-09 05:56:48

我会做这样的事情来获得没有图像的默认名称。

SELECT
    Vehicles.Id, Vehicles.VIN, COALESCE(Images.Name,'default image 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 OR Images.Name IS NULL

I'd do something like this to get the default name on ones with no image.

SELECT
    Vehicles.Id, Vehicles.VIN, COALESCE(Images.Name,'default image 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 OR Images.Name IS NULL
一场信仰旅途 2024-08-09 05:56:48

我会这样编写这个查询:

SELECT v.Id, v.VIN, 
  GROUP_CONCAT(IF(i.Default=1, i.Name, NULL)) AS ImageName,
  COUNT(i.Id) AS ImageCount
FROM Vehicles v
LEFT JOIN Images i ON (i.VehicleId = v.Id AND i.Default = 1)
GROUP BY v.Id;

我知道您希望显示图像,即使没有标记为默认值。这是执行此操作的解决方案:

SELECT v.Id, v.VIN, 
  COALESCE(i1.Name, MIN(i2.Name)) AS ImageName,
  COUNT(i2.Id) AS ImageCount
FROM Vehicles v
LEFT JOIN Images i1 ON (i1.VehicleId = v.Id AND i1.Default = 1)
LEFT JOIN Images i2 ON (i2.VehicleId = v.Id)
GROUP BY v.Id;

如果没有默认图像,您没有指定应显示哪个图像。所以我随意设计了表达式来显示按字母顺序排列的第一个图像名称。

另请注意,此查询中 i1 和 i2 之间存在笛卡尔积。我假设您不会为给定车辆标记多个默认图像,但如果您这样做,您会得到意想不到的结果,例如 ImageCount 将乘以默认图像的数量。如果每辆车最多有一张默认图像,这不是问题。

I would write this query this way:

SELECT v.Id, v.VIN, 
  GROUP_CONCAT(IF(i.Default=1, i.Name, NULL)) AS ImageName,
  COUNT(i.Id) AS ImageCount
FROM Vehicles v
LEFT JOIN Images i ON (i.VehicleId = v.Id AND i.Default = 1)
GROUP BY v.Id;

I understand that you want an image to display even if there are none marked as the default. Here's a solution that does this:

SELECT v.Id, v.VIN, 
  COALESCE(i1.Name, MIN(i2.Name)) AS ImageName,
  COUNT(i2.Id) AS ImageCount
FROM Vehicles v
LEFT JOIN Images i1 ON (i1.VehicleId = v.Id AND i1.Default = 1)
LEFT JOIN Images i2 ON (i2.VehicleId = v.Id)
GROUP BY v.Id;

You didn't specify which image it should display if none are default. So I arbitrarily designed the expression to show the image name that is first alphabetically.

Also note that there's a Cartesian product between i1 and i2 in this query. I would assume that you don't mark more than one one default image for a given vehicle, but if you do, you'll get unexpected results, e.g. ImageCount will be multiplied by the number of default images. This isn't a problem if there's at most one default image per vehicle.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文