选择相似术语的计数

Question

给定如下的mysql查询：

SELECT COUNT(*) FROM questions
WHERE questions.text LIKE "%some word%"
OR questions.text LIKE "%another word%"
OR questions.text LIKE "%yet another word%"
AND questions.field = "test"
... more WHERE conditions

返回每个LIKE术语的细目的最佳方法是什么，而不是返回与所有三个LIKE语句对应的行？

我知道可以通过为每个LIKE术语使用单独的SELECT语句来实现这一点，但是有没有办法将其封装到一条SELECT语句中，或者更短一些的语句中？

Answer 1

对于匹配值的总和，您可以使用select case

SELECT 
    sum( case when questions.text LIKE "%some word%"  then 1 else 0 end) as  test1
  , sum( case when questions.text LIKE "%another word%"  then 1 else 0 end) as  test2
  , sum( case when questions.text LIKE "%yet another word%"  then 1 else 0 end) as  test3
WHERE questions.field = "test"
 ... more WHERE conditions