mysql查询使用别名为搜索条件

最新推荐文章于 2024-06-27 10:27:17 发布

FanJizhi

最新推荐文章于 2024-06-27 10:27:17 发布

阅读量3.4k

点赞数

分类专栏： Mysql 文章标签： mysql 别名别名查询

本文链接：https://blog.csdn.net/FanJizhi/article/details/84861658

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SELECT
	a.id,
	a. STATUS,
	a.sign_photo,
	GROUP_CONCAT(sfr.file_path SEPARATOR ';') AS 'resource_id'
FROM
	order_sale a
LEFT JOIN order_logistic_data old ON a.id = old.sale_id
LEFT JOIN order_logistic_nodes oln ON oln.id = old.logistic_node_id
AND oln.node_name = '签收'
LEFT JOIN order_logistic_resource olr ON olr.logistic_data_id = old.id
LEFT JOIN sys_file_resource sfr ON olr.resource_id = sfr.id
WHERE
	a.carrier_id = '5379897b9e7e48fc92e4335599eeaabd'
AND a.create_date >= '2018-11-21 16:03:30.0 '
AND a.create_date <= '2018-12-06 16:03:30.0'
AND a.del_flag = 0
AND a.del_flag = 0
GROUP BY
	a.id
ORDER BY
	a.create_date DESC
LIMIT 10

不添加别名搜素条件时查询结果都是正确的

但是只要添加上and resource is null，resource_id一栏本该有值的也为null

通过官网发现mysql不允许使用列别名作为查询条件，最终可以使用HAVING来实现该有的查询结果

SELECT
	a.id,
	a. STATUS,
	a.sign_photo,
	GROUP_CONCAT(sfr.file_path SEPARATOR ';') AS 'resource_id'
FROM
	order_sale a
LEFT JOIN order_logistic_data old ON a.id = old.sale_id
LEFT JOIN order_logistic_nodes oln ON oln.id = old.logistic_node_id
AND oln.node_name = '签收'
LEFT JOIN order_logistic_resource olr ON olr.logistic_data_id = old.id
LEFT JOIN sys_file_resource sfr ON olr.resource_id = sfr.id
WHERE
	a.carrier_id = '5379897b9e7e48fc92e4335599eeaabd'
AND a.create_date >= '2018-11-21 16:03:30.0 '
AND a.create_date <= '2018-12-06 16:03:30.0'
AND a.del_flag = 0
AND a.del_flag = 0
GROUP BY
	a.id
HAVING
	resource_id IS NULL
ORDER BY
	a.create_date DESC
LIMIT 10