MySQL JOIN，GROUP BY，ORDER BY [英] MySQL JOIN, GROUP BY, ORDER BY

问题描述

我有一张产品表:

CREATE TABLE products (`id` INT);

以及这些产品的图片表:

And a table of images for those products:

CREATE TABLE images (`id` INT, `product_id` INT, `default` TINYINT(1));

我需要选择所有产品，并加入图像表，以便使用(default = 1)的图像，并且如果产品中没有(default = 1)的图像，则使用(default = 0)将显示在其位置.

I need to select all the products, and join the images table so that images with (default = 1) will be preferred, and if a product has no images with (default = 1), an image with (default = 0) will be shown in its place.

这是一张显示我正在寻找的图像:

Here's an image showing what I'm looking for:

现在我有这个查询:

SELECT p.id, i.id
FROM products AS p
LEFT JOIN (
    SELECT product_id, url
    FROM images
    ORDER BY default
) AS i
ON p.id = i.product_id
GROUP BY p.id
ORDER BY p.name

不优先处理默认"图像.子查询似乎没有任何作用.

Which doesn't prioritize "default" images. The subquery doesn't seem to do anything.

推荐答案

好像我只是在子查询的ORDER BY中缺少'DESC'

Looks like I was just missing a 'DESC' in the subquery's ORDER BY

:\

这篇关于MySQL JOIN，GROUP BY，ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！