在WHERE子句中使用来自SELECT的COALESCE的别名 [英] Using alias from COALESCE from SELECT in WHERE clause

问题描述

数据库小提琴

DB Fiddle

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    inbound_date VARCHAR(255),
    outbound_date VARCHAR(255)
);

INSERT INTO logistics
(id, campaign, inbound_date, outbound_date)
VALUES 
("1", "C001", "2019-01-01", "2019-02-08"),
("2", "C001", "2019-05-10", "2019-05-12"),
("3", "C001", "2019-06-12", "2019-06-15"),
("4", "C001", "2019-08-13", "2019-08-20"),
("5", "C001", "2019-11-14", "2019-11-22");

在上表中，我有日期值的inbound_date和outbound_date列.
在我的查询中，我将它们coalesce放在称为event_date的一列中.

In the table above I have the columns inbound_date and outbound_date for date values.
In my query I coalesce them to one column called event_date.

现在，我想在查询的WHERE子句中为coalesce使用别名，但出现错误Unknown column 'event_date' in 'where clause':

Now, I want to use the alias for the coalesce in the WHERE clause of my query but I get error Unknown column 'event_date' in 'where clause':

SELECT
campaign,
coalesce(inbound_date, outbound_date) as event_date
FROM logistics
WHERE
event_date BETWEEN "2019-06-01" AND "2019-10-01"

我知道我可以通过在WHERE子句中使用inbound_date和outbound_date作为两个单独的条件来解决此问题，但是使用coalesce的别名不是一种更聪明的方法吗?

I know I could solve the issue by using the inbound_date and outbound_date as two seperate conditions in the WHERE clause but isn't there a smarter way using the alias of the coalesce?

推荐答案

您可以重复表达式或使用having子句:

You can repeat the expression or use a having clause:

SELECT campaign,
       coalesce(inbound_date, outbound_date) as event_date
FROM logistics
HAVING event_date BETWEEN '2019-06-01' AND '2019-10-01';

我不建议在MySQL中使用子查询. MySQL具有实现子查询的趋势(尽管随着最新版本的改进).这可能会阻碍优化.

I don't recommend a subquery in MySQL. MySQL has a tendency to materialize subqueries (although this has improved with more recent versions). That can impede optimization.

这篇关于在WHERE子句中使用来自SELECT的COALESCE的别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！