sql - 如何检查字符串是否包含 case 语句中的单词 - SQLite

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

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)