如何使用CASE WHERE条件从表中选择主项目行?

Question

我有一个产品数组，我需要选择每个产品的主照片。我的照片表包含:id, product_id, photoname, flag, order。

'flag' = 1时为主照片，'order'为照片排序。有时'flag'和'order'是0(未设置)，所以我需要假设主照片是它的'id' ASC。

在我的MySQL代码我有:

private function getProducts($idUser) {
$products = DB::connection('mysql2')->select("SELECT 
a.*
, (CASE WHEN photos.flag = 0 THEN 
(
SELECT photoname 
FROM mpy_product_photos 
WHERE flag = 1 AND product_id = a.id LIMIT 1
) 
ELSE photos.photoname END
) AS photoname
FROM mpy_products AS `a` 
LEFT JOIN mpy_product_photos AS `photos` ON (photos.product_id = a.id) 
WHERE a.user_id = '$idUsuario' 
GROUP BY a.id 
ORDER BY a.date DESC");
return $products;
}

当相同'product_id'的所有'photoname'的'flag' = 0时，返回null。

我试着添加以下内容，以及许多东西，但总是得到语法错误。

, (CASE WHEN photos.flag = 0 THEN 
(
SELECT photoname 
FROM mpy_product_photos 
WHERE flag = 1 AND product_id = a.id LIMIT 1
) 
ELSE 
(
SELECT photoname 
FROM mpy_product_photos 
WHERE product_id = a.id ORDER BY photos.id LIMIT 1 END
) 
) AS photoname

所以当我没有'flag' = 1时，我需要选择并按'order' ASC排序。如果'order' = 0在所有行，我需要选择'id' ASC。

我也尝试了以下，至少我得到一个'photoname'随机(我猜)选择而不是null当所有'flag' = 0。

, (CASE WHEN photos.flag = 0 THEN 
(
SELECT photoname 
FROM mpy_product_photos 
WHERE product_id = a.id ORDER BY photos.flag DESC LIMIT 1
) 
ELSE photos.photoname END
) AS photoname

我该如何解决这个问题?

Answer 1

UPDATE:您明确要求使用CASE WHEN的解决方案。与此同时，我提供了替代方案。

MySQL 5.5+

select 
a.*,
(select 
photoname
from my_product_photos
where product_id = a.id
order by flag desc, ord asc, id asc
limit 1) as photoname
from my_product a;

如果你使用的是MySQL 8.0，你也可以通过RANK窗口函数来实现这一点

SELECT
product.*,
photo.photoname
FROM my_product product
JOIN (
SELECT 
*,
RANK() OVER(PARTITION BY product_id ORDER BY flag DESC, ord ASC, id ASC) AS r
FROM my_product_photos
) photo ON photo.product_id = product.id AND photo.r = 1;

RANK函数，顾名思义，根据给定的"规则"对行进行排序。查询规则为:对同一product_id的flag、order、id依次排序。这将作为别名r的列。然后，您可以像往常一样只需INNER JOIN。使用r，你可以过滤掉那些"不太可能是你想要的"。

这是一个带有示例数据的数据库。这可能与您自己的数据库中的内容不匹配，因此请根据需要进行调整。