MySQL的CASE WHEN语句在WHERE子句中导致失败

Question

我有一个复杂的查询，需要在多个列之间进行多次匹配，然后按相关性排序。

一切都很好，直到我添加了WHERE 'rank' > 0。

这时就返回一个空结果集。

如果我删除'WHERE'语句，那么我可以看到所有最高匹配结果在顶部。

能否有人帮我解决'WHERE'问题 :-D 我错在哪里了！

SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END 
+ CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
+ CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END 
+ CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END 
+ CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END 
+ CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
+ CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END 
+ CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END 
AS rank 
FROM clients 
WHERE rank > 0
ORDER BY rank

编辑

我去掉了rank一词周围的单引号，现在出现了“where子句中未知列rank”的错误。

- GrahamTheDev

---

在两个地方中删除 Rank 中的单引号并尝试。 - MusicLovingIndianGirl

如果我这样做，WHERE子句中会出现未知列等级，这至少是一些东西-我掩盖了问题-但我该怎么解决呢？:-P - GrahamTheDevRel

我认为MySQL不支持使用别名的WHERE子句-请查看https://dev59.com/WHVC5IYBdhLWcg3wvTxa - MusicLovingIndianGirl

没错，你说得对 - 当你知道怎么做时，它就很容易了 - 改为HAVING rank > 0，现在可以正常工作了。将其添加为答案，我会标记为正确 - 谢谢。 - GrahamTheDevRel

Answer 1

从rank中删除单引号并尝试。无论如何，我认为MySQL不支持带有别名的WHERE子句-请查看this。

使用HAVING而不是WHERE：

SELECT *，CASE WHEN companyName ='gfdgfs' THEN 2 ELSE 0 END + CASE WHEN companyName LIKE'% gfdgfs％' THEN 1 ELSE 0 END + CASE WHEN companyName ='potato' THEN 2 ELSE 0 END + CASE WHEN companyName LIKE'% potato％' THEN 1 ELSE 0 END + CASE WHEN address1 ='gfdgfs' THEN 2 ELSE 0 END + CASE WHEN address1 LIKE'% gfdgfs％' THEN 1 ELSE 0 END + CASE WHEN address1 ='potato' THEN 2 ELSE 0 END + CASE WHEN address1 LIKE'% potato％' THEN 1 ELSE 0 END AS rank FROM clients HAVING rank > 0 ORDER BY rank

- MusicLovingIndianGirl

Answer 2

试试这个：

SELECT * 
FROM (SELECT *, CASE WHEN companyName = 'gfdgfs' THEN 2 ELSE 0 END 
              + CASE WHEN companyName LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
              + CASE WHEN companyName = 'potato' THEN 2 ELSE 0 END 
              + CASE WHEN companyName LIKE '%potato%' THEN 1 ELSE 0 END 
              + CASE WHEN address1 = 'gfdgfs' THEN 2 ELSE 0 END 
              + CASE WHEN address1 LIKE '%gfdgfs%' THEN 1 ELSE 0 END 
              + CASE WHEN address1 = 'potato' THEN 2 ELSE 0 END 
              + CASE WHEN address1 LIKE '%potato%' THEN 1 ELSE 0 END 
              AS rank 
        FROM clients 
      ) AS A 
WHERE rank > 0
ORDER BY rank;

- Saharsh Shah

---

不行 - musiclovingindiangirl说得对，是因为WHERE不能与别名一起使用。需要使用HAVING代替。谢谢。 - GrahamTheDevRel