[大数据]mysql 查询语句 摘录

1.根据某年某月 查询数据

SELECT
	*
FROM
	表名
WHERE
	date_format(字段, '%Y-%m')= '2021-11'

2.使用group by? having 进行查询

?

?场景:如上图所示，需要 把同一天的不同 poind_id 的 fist、last? 聚合展示出来展示出来。

SELECT
	a.report_day,
	(
		(
			a.last_AR_P2_LIT1 - a.first_AR_P2_LIT1
		) * 3750 + a.total_out - a.AR_YDAY_FLOW_ACC_4
	) AS total
FROM
	(
		SELECT
			report_day,
			SUM(
				CASE point_id
				WHEN 'AR_P2_LIT01_FF' THEN
					last
				WHEN 'AR_P2_LIT02_FF' THEN
					last
				END
			) AS last_AR_P2_LIT1,
			SUM(
				CASE point_id
				WHEN 'AR_P2_LIT01_FF' THEN
					`first`
				WHEN 'AR_P2_LIT02_FF' THEN
					FIRST
				END
			) AS first_AR_P2_LIT1,
			sum(
				CASE point_id
				WHEN 'AR_P_YDAY_ACC_1' THEN
					last
				WHEN 'AR_P_YDAY_ACC_2' THEN
					last
				END
			) AS total_out,
			sum(
				CASE point_id
				WHEN 'AR_YDAY_FLOW_ACC_4' THEN
					last
				END
			) AS AR_YDAY_FLOW_ACC_4
		FROM
			disp_device_data_set_day
		WHERE
			date_format(report_day, '%Y-%m') = '2021-11'
		GROUP BY
			report_day
		HAVING
			last_AR_P2_LIT1 IS NOT NULL
	) AS a

最终查询结果

?