如何从以下mysql查询的where子句中引用'decider'?

问题描述：

How to refer 'decider' in the where clause from the following mysql query?

SELECT *, 
       CASE
         WHEN (cond1) THEN 1
         WHEN (cond2) THEN 2
       END as decider
  FROM t1,
       t2 
 WHERE cond12
   AND decider <> NULL

我尝试了，但出现了1054: Unknown column in where clause错误.

I tried it, and I got a 1054: Unknown column in where clause error.

答

使用:

SELECT *, 
       CASE
         WHEN (cond1) THEN 1
         WHEN (cond2) THEN 2
         ELSE NULL
       END as decider
  FROM t1,
       t2 
 WHERE cond12
HAVING decider IS NOT NULL

最早允许您使用列别名的MySQL是GROUP BY子句
您需要使用IS NULL或IS NOT NULL(在适当情况下)，因为NULL不是值-它是缺少任何值的占位符，这需要在SQL中进行特殊处理

The earliest MySQL allows you to use column aliases is the GROUP BY clause
You need to use IS NULL or IS NOT NULL (where appropriate) because NULL is not a value -- it's a placeholder for the lack of any value, which requires special handling in SQL

如何从以下mysql查询的where子句中引用'decider'?

相关推荐