您的第二次尝试走在正确的轨道上,使用逻辑AND/OR
分组而不是CASE
,但如果你想prefer行匹配cmp_brand
覆盖有空的行cmp_brand
并期望只返回一个结果,构建你的ORDER BY
对非空进行排序cmp_brand
首先,将总体结果限制为 1。
SELECT thumb
FROM inf_brand_images
WHERE
is_active=1 AND
((cmp_brand = '' AND brand='NIKE') OR (cmp_brand='123_NIKE'))
/* non-empty cmp_brand will sort first */
ORDER BY cmp_brand <> '' DESC
/* and the end result is limited only to the first sorted row
which will be the cmp_brand if matched, or the brand otherwise */
LIMIT 1
http://sqlfiddle.com/#!2/d176b/2
这是有效的,因为表达式cmp_brand <> ''
计算结果为布尔值true/false
,MySQL 将其解释为1/0
。对这些值进行降序排序会强制非空值先排序(1 在 0 之前)。
评论后更新:
由于您确实有可能返回多行,因此您不能依赖ORDER BY
。相反,您可以执行LEFT JOIN
针对同一张桌子。一方面,匹配cmp_brand = ''
另一边比赛cmp_brand = '123_NIKE'
。重要的是,返回thumb
专栏来自both连接的两侧。
将其包装在子查询中FROM
子句,那么在顶层你可以使用SELECT CASE
更喜欢cmp_brand
如果非空。
SELECT DISTINCT
CASE WHEN cbcb IS NOT NULL THEN cbthumb ELSE bthumb END AS thumb
FROM (
/* Return thumbs from both sides of the join */
SELECT
b.thumb AS bthumb,
b.cmp_brand AS bcb,
cb.thumb AS cbthumb,
cb.cmp_brand AS cbcb
FROM
inf_brand_images b
/* join the table against itself with the matching cmp_brand in the join condition */
LEFT JOIN inf_brand_images cb
ON b.brand = cb.brand
AND cb.cmp_brand = '123_NIKE'
WHERE
/* The WHERE clause looks for empty cmp_brand on the left side of the join */
b.brand = 'NIKE' AND b.cmp_brand = ''
) thumbs
- 以下是 123_NIKE 匹配的示例:http://sqlfiddle.com/#!2/dfe228/31
- 124_NIKE 不匹配的示例:http://sqlfiddle.com/#!2/dfe228/32