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

时间:2022-01-01 11:58:41

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 9

ORDER 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 9

ORDER BY得分DESC LIMIT 9