问题很简单,正如标题所说,但这是一种逻辑.
这是我的代码

CREATE TABLE `inf_brand_images` (
`id` bigint(99) NOT NULL AUTO_INCREMENT,
`brand` varchar(255) NOT NULL,
`thumb` text NOT NULL,
`is_active` int(2) NOT NULL DEFAULT '1',
`cmp_brand` varchar(1024) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6458 DEFAULT CHARSET=latin1

这是此表中的数据

ID | brand  | thumb  |is_active| cmp_brand
1  | NIKE   | a.png  | 1       | 
2  | DUNHILL| b.png  | 1       |
3  | NIKE   | c.png  | 1       | 123_NIKE
4  | NIKE   | d.png  | 1       | 789_NIKE

在我的情况下,cmp_brand的前缀是一些ID,例如123_和789_.
现在如果我搜索NIKE,那么我有两个参数,一个是NIKE,另一个是id_NIKE.其中id可能是123或456或任何其他.所以对于NIKE搜索,我有两个参数,一个是brand = NIKE,另一个是cmp_brand = 123_NIKE(我可以自动将ID与品牌名称关联起来)

我想要的是

IF cmp_brand is '' then compare with brand ELSE compare brand AND cmp_brand.

这是我尝试过的,都无法正常工作

SELECT thumb 
FROM inf_brand_images 
where is_active=1  AND 
CASE WHEN cmp_brand = '' THEN brand='NIKE' 
ELSE cmp_brand='123_NIKE' END

 SELECT thumb 
 FROM inf_brand_images 
 where is_active=1 AND 
((cmp_brand = '' AND brand='NIKE') OR (cmp_brand='123_NIKE'))

解决方法:

您使用逻辑AND / OR分组而不是CASE进行第二次尝试时处于正确的轨道,但是,如果您希望将匹配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.对这些值进行降序排序将迫使非空值对拳头排序(0之前为1).

评论后更新：

由于您确实有可能返回多于一行,因此您不能依赖ORDER BY.相反,您可以对同一张表执行LEFT JOIN.一方面,匹配cmp_brand =”,另一方面,匹配cmp_brand =’123_NIKE’.重要的是,从联接的两侧返回拇指列.

将其包装在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