我正在尝试使用CASE WHEN来计算varchar列中是否包含特定文本。我一直试图破解它，但是添加第二，第三种情况时出现问题。
我使用和工作的代码是：
SELECT *,
CASE
WHEN (Orders.Some_text LIKE "%test%" AND Orders.Some_text NOT LIKE "%found%") THEN 1
ELSE 0 END AS Test
FROM Orders;
现在，我添加第二种情况：
SELECT *,
CASE
WHEN (Orders.Some_text LIKE "%test%" AND Orders.Some_text NOT LIKE "%found%") THEN 1
WHEN (Orders.Some_text LIKE "%test%" AND Orders.Some_text NOT LIKE "%missing%") THEN 1
ELSE 0 END AS Test
FROM Orders;
并且它在“测试”列中产生错误。
我想要的结果只是简单的1，当找到单词测试时，却没有发现，丢失或/和期间。
+------+--------------+------------+
| id | Some_text | Test |
+------+--------------+------------+
| 1 | test | 1 |
| 2 | test found | 0 |
| 3 | found test | 0 |
| 4 | test missing | 0 |
| 5 | missing test | 0 |
| 6 | test during | 0 |
| 7 | during test found | 0 |
| 8 | abc | 0 |
+------+--------------+------------+
复制我的数据集的代码：
CREATE TABLE Orders
(
id INT,
Some_text char(255));
insert into Orders values (1, "test");
insert into Orders values (2, "test found");
insert into Orders values (3, "found test");
insert into Orders values (4, "test missing");
insert into Orders values (5, "miss ing test");
insert into Orders values (6, "test during");
insert into Orders values (7, "during test found");
insert into Orders values (8, "abc");
最佳答案
似乎您需要检查字段中是否同时没有单词found和missing，然后将值设置为1。
合并如下所示的语句，它应该返回预期的输出。
SELECT *,
CASE
WHEN (Orders.Some_text LIKE "%test%" AND
Orders.Some_text NOT LIKE "%found%" AND
Orders.Some_text NOT LIKE "%missing%") THEN 1
ELSE 0 END AS Test
FROM Orders;
输出量
+------+--------------+------------+
| id | Some_text | Test |
+------+--------------+------------+
| 1 | test | 1 |
| 2 | test found | 0 |
| 3 | found test | 0 |
| 4 | test missing | 0 |
| 5 | missing test | 0 |
| 6 | test during | 1 |
| 7 | during test found | 0 |
| 8 | abc | 0 |
关于mysql - MySQL-varchars的多种情况，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53279291/