喜欢和不喜欢在一个mysql查询中

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

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

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

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

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 9

ORDER BY得分DESC LIMIT 9

#1