MySQL连接、分组和排序

Question

我有一个产品表：

CREATE TABLE products (`id` INT);

以下是这些产品的图片表格：

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

我需要选择所有产品，并加入图片表，使带有（default = 1）的图像优先显示，如果某个产品没有带有（default = 1）的图像，则应在其位置显示具有（default = 0）的图像。

---

这里是显示我所寻找内容的图片：

---

目前我有以下查询语句：

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

这并没有优先考虑“默认”图片。子查询似乎没有起到任何作用。

- kryo

Answer 1

SQLFiddle演示

select products.id,
       coalesce(t1.mid,t2.mid) as image_id      

from products
left join (select min(id) mid,product_id 
                  from images where `default`=1
                  group by product_id ) t1
        on products.id=t1.product_id
left join (select min(id) mid,product_id 
                  from images where `default`=0
                  group by product_id ) t2
        on products.id=t2.product_id

- valex

---

这个查询非常慢。我进行了优化：sqlfiddle.com/#!2/4d948/6/0 但在我的测试中，如果需要除了product_id和image_id以外的任何列，它仍然比我的查询要慢。 - kryo

Answer 2

看起来我在子查询的ORDER BY中只是缺少了一个'DESC'

:\

- kryo

Answer 3

如果您想为产品仅显示一张图片，请尝试以下查询 -

SELECT p.*, i.* FROM products p
  JOIN (SELECT * FROM images ORDER BY `default` DESC) i
    ON p.id = i.product_id
GROUP BY p.id

- Devart

Answer 4

试试这个：

SELECT 
  p.id productid, 
  IFNULL(i1.id, i2.id)
FROM products    AS p
LEFT JOIN images AS i1  ON p.id = i1.product_id
                       AND i1.`Default` = 1  
LEFT JOIN images AS i2  ON p.id = i2.product_id
                       AND i2.`Default` = 0
GROUP BY p.id;

- Mahmoud Gamal

---

这个查询比我的查询更有效吗？ - kryo

@kryo 这总是要看情况的。但我认为你的会更好，因为它只涉及一个 JOIN。 - Mahmoud Gamal

看起来我的13个“样品”产品和19个“样品”图片差不多。看来我们要在上线后再决定... :D - kryo