使用合并或 if 导致 where 子句 mysql [英] using coalesce or if results in where clause mysql

问题描述

我使用一些这样的 sql:

I use some sql like this:

SELECT COALESCE(group.display,item.display) as display....

我想在 WHERE 子句中添加:

I would like to add in the WHERE clause:

WHERE display='1'

WHERE 显示的是合并的结果.

WHERE display is the result of the coalesce.

同样，我希望能够对这样的事情做同样的事情:

Similarly I'd like to be able to do the same with something like this:

IF(ISNULL(gd.group_main_image),p.main_image,gd.group_main_image) AS image
... WHERE image IS NOT NULL

请问我该怎么做?

推荐答案

不能在同一级别的查询中使用别名.

You can't use aliases in the same level of query.

你必须重复一遍.

WHERE COALESCE(group.display,item.display) = '1'

编辑

好吧，我太严格了.您可以在 MySql 中的 having 子句中使用别名.您不能在其他 DBMS(Oracle、SQL Server)中做到这一点.通常在 ANSI SQL 中也是不允许的.

Well, I've been too restrictive. You can use alias in an having clause in MySql. You can't do that in other DBMS (Oracle, SQl Server). Generally it's also not permitted in ANSI SQL.

这篇关于使用合并或 if 导致 where 子句 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！