mysql 中别名参与运算报错解决方法

SELECT
		dayss,
		mch,
		CONVERT ( SUM( income ), DECIMAL ( 10, 2 ) ) AS incomes,
		CONVERT ( SUM( refund ), DECIMAL ( 10, 2 ) ) AS refund,
		CONVERT ( income * CAST( '0.006' AS DECIMAL ( 18, 4 ) ), DECIMAL ( 10, 2 ) ) AS service_charge ,
	(
		incomes - refund - service_charge ) 
	) AS total
		
	FROM

运行时会出现报找不到 incomes和service_charge 的错，是因为聚合或函数的字段是平级的，分辨不出彼此的别名。只能在外侧包一层。

SELECT
	incomes,
	refund,
	service_charge ,
	(incomes - refund - service_charge) as total 
FROM
	(
	SELECT
		dayss,
		mch,
		CONVERT ( SUM( income ), DECIMAL ( 10, 2 ) ) AS incomes,
		CONVERT ( SUM( refund ), DECIMAL ( 10, 2 ) ) AS refund,
		CONVERT ( income * CAST( '0.006' AS DECIMAL ( 18, 4 ) ), DECIMAL ( 10, 2 ) ) AS service_charge