php - MYSQL 使用 SUB SELECT TO WHERE 子句

Question

您好，我想问是否可以在 where 子句中调用我的子选择，例如 HELPER1 将用作 WHERE HELPER1 LIKE 'SAMPLE%'

这是我的示例查询

SELECT dispatch_id, FROM_UNIXTIME(expected_departure_date, '%Y-%m%-%d %h:%i:%s') as dt,
FROM_UNIXTIME(expected_wh_arrival, '%Y-%m%-%d %h:%i:%s') as at, truck, FROM_UNIXTIME(created_on, '%Y-%m%-%d %h:%i:%s') as created,remarks, agent_id,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=helper1_id) as hp1,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=helper2_id) as hp2,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=helper3_id) as hp3,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=helper4_id) as hp4,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=helper5_id) as hp5,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=driver) as driver1,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=driver2) as driver2,
(SELECT fullname FROM dev.tpl_user_profiles WHERE uid=dispatcher_uid) as dispatcher,
(SELECT td.destination FROM dev.tpl_destination as td WHERE td.id=tdd.destination ) as dest,
(SELECT delivery_type FROM dev.tpl_delivery_type WHERE id=delivery_type ) as det,
 (SELECT plate_number FROM dev.tpl_trucks  WHERE id=truck ) as pn ,
 status
from dev.tpl_dispatch as tdd WHERE
EXISTS (SELECT td.destination FROM dev.tpl_destination as td WHERE td.destination LIKE 'Ac%')

Answer 1

没有。 SQL 不允许在定义列别名的 SELECT 的 WHERE 中使用列别名。

我可以想到三个选择:

1. 使用子查询。但这会增加实现子查询的开销(在 MySQL 中，而不是在其他数据库中)。
2. 重新排列查询以使用LEFT JOIN而不是子查询。但这是一项艰巨的工作。
3. 使用 MySQL 扩展HAVING。

在非聚合查询中，MySQL 允许您使用引用列别名的 HAVING 子句。所以你可以添加:

HAVING HELPER1 LIKE 'SAMPLE%'