prod_catg LIKE '1,%' --matches when 1 is the first category
OR prod_catg LIKE '%,1,%' --matches when 1 is somewhere in the middle
OR prod_catg LIKE '%,1' --matches 1 when is the last category
无论如何,您最好通过添加类别表以及产品(主)表上对它的引用来重构您的架构
EDIT
面对这个问题的另一种方法是使用REGEXP这将导致更短的WHERE
子句(这是我用来测试的):
DECLARE @regexp VARCHAR(100);
SET @regexp = '^1,.*|.*,1$|.*,1,.*';
SELECT
'1,11,15,51,22,31' REGEXP @regexp AS test1,
'51,11,15,1,22,31' REGEXP @regexp AS test2,
'11,15,51,22,31,1' REGEXP @regexp AS test3,
'7,11,15,51,22,31' REGEXP @regexp AS test4,
'51,11,15,7,22,31' REGEXP @regexp AS test5,
'11,15,51,22,31,7' REGEXP @regexp AS test6;
这将匹配你的prod_catg
反对正则表达式'^1,.*|.*,1$|.*,1,.*'
返回1 (TRUE)
如果匹配的话0 (FALSE)
否则。
那么你的 WHERE 子句将如下所示:
WHERE prod_catg REGEXP '^1,.*|.*,1$|.*,1,.*'
正则表达式的解释:
^1,.* --matches 1 at the beginning of a string followed by a `,` and any other char
.*,1$ --matches 1 at the end of a string preceded by a `,` and any other char
.*,1,.* --matches 1 between two `,` which are sourrounded by any other chars
| --is the OR operator
我确信这个正则表达式可以更紧凑,但我不太擅长正则表达式
显然你可以改变你在正则表达式中寻找的类别(尝试替换1
with 7
在上面的例子中)