如何在SQLite的case语句中检查字符串是否包含某个单词

Question

这是我的代码：

SELECT 
CASE your_text
  WHEN contains('hate') THEN 'hater'
  WHEN contains('love') THEN 'lover'
  ELSE 'other'
END haterOrLover,
count(*) AS total_count
FROM a_table
GROUP BY haterOrLover

如果只有contains()存在！

我想要计算有多少个讨厌者和爱好者，并且结果应该呈现以下格式：

+--------------+-------------+  
| haterOrLover | total_count |  
+--------------+-------------+  
| hater        | 1           |  
+--------------+-------------+  
| lover        | 2           |  
+--------------+-------------+  
| other        | 1           |  
+--------------+-------------+

尝试过：

WHEN your_text like '%hate%' THEN 'hater'

WHEN '%hate%' THEN 'hater'

但是没有起作用。

如何检查记录中的“your_text”列是否包含特定单词？

编辑
如何复现：

CREATE TABLE a_table
(
id         char(10)      PRIMARY KEY,
your_text  char(250)     NOT NULL
);

INSERT INTO a_table
    (id, your_text)
VALUES
    (1, 'I love dogs'),
    (2, 'I hate cats'),
    (3, 'I like rabits'),
    (4, 'I love haskis');

感谢您

---

解决方案:

SELECT 
CASE
  WHEN your_text like '%hate%' THEN 'hater'
  WHEN your_text like '%love%' THEN 'lover'
  ELSE 'other'
END haterOrLover,
count(*) AS total_count
FROM a_table
GROUP BY haterOrLover

- Thanasis Mattas

---

https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query - Yunnosch

Answer 1

试一试：

with cte as (
SELECT 
CASE when your_text like ('%hate%') THEN 'hater'
     when your_text like ('%love%') THEN 'lover'
     ELSE 'other'
     END haterOrLover FROM a_table )
select haterOrLover , count(*) as total_count from cte
group by (haterOrLover)

- nimajv

---

谢谢，那个方法奏效了！实际上，在初始代码中，CASE后面的'your_text'是错误的。 - Thanasis Mattas