MySQL Case语句-其中的未知列

I have a problem with my query, what's wrong?

我的问题是，怎么了?

SELECT 
    CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END as id_ranking
FROM cp_pessoa 
    WHERE id_ranking IN (1,2);

I get this error: #1054 - Unknown column 'id_ranking' in 'where clause'

我得到这个错误:#1054 -在where子句中不知道的列'id_ranking'

Thank you!

谢谢你！

3 个解决方案

#1

It's because SELECT prepare result (as id_ranking) AFTER than WHERE was called.

这是因为在调用之后选择prepare结果(作为id_ranking)。

SELECT 
    CASE 
    WHEN cp_pessoa.score < 100 THEN 1 
    WHEN cp_pessoa.score < 300 THEN 2 
    WHEN cp_pessoa.score >= 300 THEN 3 
END as id_ranking
FROM cp_pessoa 
WHERE cp_pessoa.score < 300;

This is how that works (order):

这就是它的工作原理(顺序):

FROM clause
从条款
WHERE clause
WHERE子句
GROUP BY clause
GROUP BY子句
HAVING clause
有条款
SELECT clause
SELECT子句
ORDER BY clause
ORDER BY子句

#2

Aliases are not allowed in where. Change query to either

在其中不允许使用别名。要么改变查询

SELECT * FROM 
(
SELECT 
    CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END as id_ranking
FROM cp_pessoa 
)a 
WHERE id_ranking IN (1,2)

或

SELECT 
    CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END as id_ranking
FROM cp_pessoa 
WHERE 
  CASE 
        WHEN cp_pessoa.score < 100 THEN 1 
        WHEN cp_pessoa.score < 300 THEN 2 
        WHEN cp_pessoa.score >= 300 THEN 3 
    END IN (1,2)

#3

You can't use an alias in the where clause, instead try

您不能在where子句中使用别名，而是尝试使用

SELECT 
CASE 
    WHEN cp_pessoa.score < 100 THEN 1 
    WHEN cp_pessoa.score < 300 THEN 2 
    WHEN cp_pessoa.score >= 300 THEN 3 
END as id_ranking
FROM cp_pessoa 
WHERE cp_pessoa.score < 300

#1