I want to do a query containing 'like' and 'not like'.
我想做一个包含'like'和'not like'的查询。
Current example: i want everything starting with '1|%' but not with '1|6|199|%' or '1|6|200|%'.
当前示例:我希望所有内容都以“1 |%”开头,而不是“1 | 6 | 199 |%”或“1 | 6 | 200 |%”。
Current query:
当前查询:
'SELECT * FROM `links` WHERE `category` LIKE '1|%' NOT LIKE '1|6|199|%','1|6|200|%' ORDER BY `score` DESC LIMIT 9'.
But that doesn't work. Any tips? thx
但这不起作用。有小费吗?谢谢
4 个解决方案
#1
29
Just add "and category"...
只需添加“和类别”......
SELECT * FROM links
WHERE category LIKE '1|%'
AND category NOT LIKE '1|6|199|%','1|6|200|%'
ORDER BY score DESC LIMIT 9
Actually, the comma separated condition is not a syntax I'm familiar with. If that's not working, try this instead:
实际上,逗号分隔条件不是我熟悉的语法。如果那不起作用,请尝试这样做:
SELECT * FROM links
WHERE category LIKE '1|%'
AND category NOT LIKE '1|6|199|%'
AND category NOT LIKE '1|6|200|%'
ORDER BY score DESC LIMIT 9
#2
3
You can use regexps
:
你可以使用正则表达式:
SELECT *
FROM links
WHERE category LIKE '1|%'
AND category NOT REGEXP '^1\\|6\\|(199|200)\\|'
ORDER BY
score DESC
LIMIT 9
Note that REGEXP
's don't use indexes, while LIKE
does.
请注意,REGKEP不使用索引,而LIKE则使用索引。
In this query, LIKE '1|%'
will serve as a coarse filter using the index on category
if any, while REGEXP
's will fine filter the results.
在此查询中,LIKE'1 |%'将用作使用类别索引的粗略过滤器(如果有),而REGEXP将精细过滤结果。
#3
2
I think a bigger problem is that you have de-normalized tables. The correct answer would be to normalize your tables.
我认为更大的问题是你已经对表进行了非规范化。正确的答案是规范化你的表格。
But if you can't do that, you should use commas as separators and FIND_IN_SET()
instead:
但是如果你不能这样做,你应该使用逗号作为分隔符,而使用FIND_IN_SET():
WHERE FIND_IN_SET('1', category) > 1
AND FIND_IN_SET('6', category) > 1
AND FIND_IN_SET('199', category) = 0
AND FIND_IN_SET('200', category) = 0
#4
0
It is also possible to use two inner join, probably not the best solution for this query, but could still be useful.
也可以使用两个内连接,可能不是此查询的最佳解决方案,但仍然有用。
SELECT * FROM links
SELECT * FROM链接
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|199|%') AS escl1 ON (links.category=escl1.category)
INNER JOIN(SELECT * FROM links WHERE category NOT LIKE'1 | 6 | 199 |%')AS escl1 ON(links.category = escl1.category)
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|200|%') AS escl2 ON (links.category=escl2.category)
INNER JOIN(SELECT * FROM links WHERE category NOT LIKE'1 | 6 | 200 |%')AS escl2 ON(links.category = escl2.category)
WHERE category LIKE '1|%'
WHERE类别LIKE'1 |%'
ORDER BY
score
DESC LIMIT 9ORDER BY得分DESC LIMIT 9
#1
29
Just add "and category"...
只需添加“和类别”......
SELECT * FROM links
WHERE category LIKE '1|%'
AND category NOT LIKE '1|6|199|%','1|6|200|%'
ORDER BY score DESC LIMIT 9
Actually, the comma separated condition is not a syntax I'm familiar with. If that's not working, try this instead:
实际上,逗号分隔条件不是我熟悉的语法。如果那不起作用,请尝试这样做:
SELECT * FROM links
WHERE category LIKE '1|%'
AND category NOT LIKE '1|6|199|%'
AND category NOT LIKE '1|6|200|%'
ORDER BY score DESC LIMIT 9
#2
3
You can use regexps
:
你可以使用正则表达式:
SELECT *
FROM links
WHERE category LIKE '1|%'
AND category NOT REGEXP '^1\\|6\\|(199|200)\\|'
ORDER BY
score DESC
LIMIT 9
Note that REGEXP
's don't use indexes, while LIKE
does.
请注意,REGKEP不使用索引,而LIKE则使用索引。
In this query, LIKE '1|%'
will serve as a coarse filter using the index on category
if any, while REGEXP
's will fine filter the results.
在此查询中,LIKE'1 |%'将用作使用类别索引的粗略过滤器(如果有),而REGEXP将精细过滤结果。
#3
2
I think a bigger problem is that you have de-normalized tables. The correct answer would be to normalize your tables.
我认为更大的问题是你已经对表进行了非规范化。正确的答案是规范化你的表格。
But if you can't do that, you should use commas as separators and FIND_IN_SET()
instead:
但是如果你不能这样做,你应该使用逗号作为分隔符,而使用FIND_IN_SET():
WHERE FIND_IN_SET('1', category) > 1
AND FIND_IN_SET('6', category) > 1
AND FIND_IN_SET('199', category) = 0
AND FIND_IN_SET('200', category) = 0
#4
0
It is also possible to use two inner join, probably not the best solution for this query, but could still be useful.
也可以使用两个内连接,可能不是此查询的最佳解决方案,但仍然有用。
SELECT * FROM links
SELECT * FROM链接
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|199|%') AS escl1 ON (links.category=escl1.category)
INNER JOIN(SELECT * FROM links WHERE category NOT LIKE'1 | 6 | 199 |%')AS escl1 ON(links.category = escl1.category)
INNER JOIN (SELECT * FROM links WHERE category NOT LIKE '1|6|200|%') AS escl2 ON (links.category=escl2.category)
INNER JOIN(SELECT * FROM links WHERE category NOT LIKE'1 | 6 | 200 |%')AS escl2 ON(links.category = escl2.category)
WHERE category LIKE '1|%'
WHERE类别LIKE'1 |%'
ORDER BY
score
DESC LIMIT 9ORDER BY得分DESC LIMIT 9