背景介绍
昨天在写sql脚本,与前端进行调试的时候发现,SQL中的字段别名在查询的时候竟然不识别,代码如下:
<select id="getDiscountUnionListByPage" resultType="Map">
select
dis.merchant_id as merchantId,
mer.name as merchantName,
mer.address as merchantAddress,
dis.id as couponId,
discount_price as couponPrice,
activity_end_date as couponEndDate,
discount_name as couponName,
discount_start as couponStart,
discount_rest as couponNum,
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((#{longitude}*PI()/180-mer.longitude*PI()/180)/2),2)
+COS(#{longitude}*PI()/180)*COS(mer.longitude*PI()/180)*POW(SIN((#{latitude}*PI()/180-mer.latitude*PI()/180)/2),2)))*1000)
AS distance
from tb_discount dis
left join tb_union_merchant mer on dis.merchant_id = mer.id
<where>
del_flag = '0'
and putaway_flag = '1'
and vouchers_create = '3'
and is_union ='1'
and mer.industry_first = #{industryFirst}
and mer.industry_twice != #{industrySecond}
and distance <= 3000
</where>
order by distance asc
</select>
其中distance为字段别名,表示的是根据经纬度计算的距离,报错如下:
问题解决
很显然,这里的distance别名是不被识别的,当时的紧急做法就是将脚本里面的distance都换成表达式,果然问题得到了解决,但是很纳闷的是为什么distance字段不能被识别呢(其实,这里面有两个distance,where语句中的distance是不被识别的,但是order by的是可以识别,当时以为orderby 也不能识别别名字段,浪费了好多时间查资料)。
解决方法:
方式一:将where中的别名换成表达式即可解决
方式二:使用子查询的方式,然后就可以在where子句中调用了
SELECT
type,id,merchantId,name, address,industryFirst,productPrice,discountPrice,integral,distance,volume from (
SELECT
'1' AS type,product.id AS id,product.merchant_id merchantId,product.product_name AS name, '' AS address, '' AS industryFirst,
product.product_price AS productPrice,product.discount_price AS discountPrice,product.exchange_integral AS integral,
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((#{longitude}*PI()/180-longitude*PI()/180)/2),2)
+COS(#{longitude}*PI()/180)*COS(longitude*PI()/180)*POW(SIN((#{latitude}*PI()/180-latitude*PI()/180)/2),2)))*1000)
AS distance,
COUNT(record.id) AS volume
FROM tb_union_merchant_product product
LEFT JOIN tb_union_merchant merchant ON product.merchant_id = merchant.id
LEFT JOIN tb_union_merchant_examine examine ON merchant.id = examine.merchant_id
LEFT JOIN tb_union_product_record record ON product.id = record.product_id
WHERE distinguish= #{distinguish}
AND merchant.status ='1' AND merchant.longitude IS NOT NULL
AND merchant.latitude IS NOT NULL AND examine.examine_status='1'
AND product.product_name LIKE '%${keyword}%'
and product.product_states = '1'
and product.del_flag = '0'
GROUP BY product.id
order by distance ,volume DESC
问题拓展
为什么where子句中无法使用到字段别名作为筛选条件呢
- 这就涉及到了SQL语句的整个执行顺序,如下表:
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
执行顺序依次为:
- from :先确定查询范围
- ON:确定多表联合查询的条件
- JOIN:指定联合哪些数据表
- WHERE :全表查询的筛选条件,生成第一个结果集
- GROUP BY:分组条件,对第一个结果集进行分组,得到第二个结果集
- HAVING :过滤条件,与group by进行连用,对第二个结果集中的每组数据,进行筛选过滤,得到第三个结果集
- SELECT:指定获取的列项,得到第四个结果集
- DISTINCT :对指定列进行去重操作
- ORDER BY:对结果集按照指定字段进行排序整理
- LIMIT:对最终结果集进行截取,一般和offset连用,可用于分页
所以,以此可以看出,为什么在where语句中没法使用查询列的别名进行过滤了,因为调用where子句的时候,select子句还没有开始执行,所以不识别,同理,order by子句中是可以使用
在查询mysql的字段别名使用的时候,翻到了mysql的官方文档,里面对别名的使用场景进行了简要的介绍:
意思就是别名可以使在order by、having、group by 子句中,但是根据上面的SQL执行过程,很明显group by 和 having都在select之前啊,这里值得注意的是,mysql对group by 进行了优化加强,所以在group by子句中可以使用别名进行分类,但是其他数据库还是遵循着SQL的执行顺序
总结
都是因为字段别名的问题展开的一系列思考,大多也还是基础知识吧,稍微再巩固一下,继续加油啦各位!