关于mysql查询语句的一次问题记录

April 03, 2018 - 3 min read

最近在开发一个公司内部使用的财务报表系统,在一次查询时数据库报了一个错误,在网上查看原因并解决问题之后顺便记录一下,该sql的目标是从日报主表,日报月数据表,日报模块表中查询到指定经营公司,指定日期的当日汇总数据(包含部分当月数据用于后续计算)

问题sql如下:

SELECT
	A1.depcode AS depcode,
	SUM(d_dd_xcddsntq) AS sntq,
	(
		SELECT
			(
				SUM(d_jk_scddldl) + SUM(d_jk_zcddldl) + SUM(d_jk_hdjkl)
			) AS bysj
		FROM
			mall_daily A
		LEFT JOIN mall_daily_jk B ON A.id = B.d_id
		LEFT JOIN mall_daily_month C ON A.depcode = C.depcode
		AND A.d_datetime >= C. START
		AND A.d_datetime <= C.
		END
		WHERE
			A.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d')
		AND A.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d')
		AND A.depcode = A1.depcode
	) AS aj13,
	(
		SELECT
			SUM(B.d_dd_xcztdd) AS bysj
		FROM
			mall_daily A
		LEFT JOIN mall_daily_dd B ON A.id = B.d_id
		LEFT JOIN mall_daily_month C ON A.depcode = C.depcode
		AND A.d_datetime >= C. START
		AND A.d_datetime <= C.
		END
		WHERE
			A.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d')
		AND A.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d')
		AND C.table_name = 'DailyDd'
		AND A.depcode = A1.depcode
	) AS aj25,
	TRUNCATE (
		(SELECT aj25) / (SELECT aj13),
		2
	) AS bysj,
	D.d_month_byjh AS byjh,
	TRUNCATE (
		(SELECT bysj) / (SELECT byjh),
		2
	) AS jhdcl,
	TRUNCATE (30 / 31, 2) AS sjjd,
	TRUNCATE (
		(SELECT bysj) / (SELECT sntq),
		2
	) AS tb
FROM
	mall_daily A1
LEFT JOIN mall_daily_dd B ON A1.id = B.d_id
LEFT JOIN mall_daily_jk C ON A1.id = C.d_id
LEFT JOIN mall_daily_month D ON A1.depcode = D.depcode
AND A1.d_datetime >= D. START
AND A1.d_datetime <= D.
END
WHERE
	A1.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d')
AND A1.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d')
GROUP BY
	A1.depcode;

上述sql在执行时提示

[Err] 1247 - Reference 'sntq' not supported (reference to group function)

大意是不支持'sntq'的引用,但是上面SELECT的字段中明明有这个alias。后来临时通过在这个位置用表达式替换了别名解决(将SELECT sntq替换为SUM(d_dd_xcddsntq)),但始终觉得不完美,最后google以下找到了更好的解决方式,就是在这个查询语句外层再套一层查询语句,也就是将当前查询语句作为另一个语句的子查询,修改后结构如下:

SELECT
	depcode,
	sntq,
	bysj,
	byjh,
	jhdcl,
	sjjd,
	TRUNCATE (bysj / sntq, 2) AS tb
FROM
	(
		SELECT
			A1.depcode AS depcode,
			SUM(d_dd_xcddsntq) AS sntq,
			(
				SELECT
					(
						SUM(d_jk_scddldl) + SUM(d_jk_zcddldl) + SUM(d_jk_hdjkl)
					) AS bysj
				FROM
					mall_daily A
				LEFT JOIN mall_daily_jk B ON A.id = B.d_id
				LEFT JOIN mall_daily_month C ON A.depcode = C.depcode
				AND A.d_datetime >= C. START
				AND A.d_datetime <= C.
				END
				WHERE
					A.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d')
				AND A.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d')
				AND A.depcode = A1.depcode
			) AS aj13,
			(
				SELECT
					SUM(B.d_dd_xcztdd) AS bysj
				FROM
					mall_daily A
				LEFT JOIN mall_daily_dd B ON A.id = B.d_id
				LEFT JOIN mall_daily_month C ON A.depcode = C.depcode
				AND A.d_datetime >= C. START
				AND A.d_datetime <= C.
				END
				WHERE
					A.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d')
				AND A.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d')
				AND C.table_name = 'DailyDd'
				AND A.depcode = A1.depcode
			) AS aj25,
			TRUNCATE (
				(SELECT aj25) / (SELECT aj13),
				2
			) AS bysj,
			D.d_month_byjh AS byjh,
			TRUNCATE (
				(SELECT bysj) / (SELECT byjh),
				2
			) AS jhdcl,
			TRUNCATE (30 / 31, 2) AS sjjd
		FROM
			mall_daily A1
		LEFT JOIN mall_daily_dd B ON A1.id = B.d_id
		LEFT JOIN mall_daily_jk C ON A1.id = C.d_id
		LEFT JOIN mall_daily_month D ON A1.depcode = D.depcode
		AND A1.d_datetime >= D. START
		AND A1.d_datetime <= D.
		END
		WHERE
			A1.d_date >= DATE_FORMAT('2018-03-01', '%Y-%m-%d')
		AND A1.d_date <= DATE_FORMAT('2018-03-21', '%Y-%m-%d')
		GROUP BY
			A1.depcode
	) t;

参考链接