sql报表总结

前言：

报表中最常使用where字句、group by 分组、having分组过滤、union、union all、子查询、with rollup、以及内置函数等。

1. Where 字句使用(简单讲下)

项目中使用Mybatis持久层框架，优点是SQL灵活，缺点对开发人员SQL编写要求比较高。

       一般where字句建议<if test=””></if>这种动态SQL,避免直接拼接SQL条件，可以使SQL复用。当然mybatis动态SQL标签也很多，可以参考文档了解下。

1. Group By 使用(简单讲下)

Group By在报表SQL中使用频次最高关键字，顾名思义是用来分组统计的，在使用时，大家应该知道Group By 字段，字段 …,在Select 字段，字段，…,聚合函数，例如count，max, min, sum, avg等等。

1. Having分组过滤

Having 是用来对分组后的数据进行过滤。

1. Union和Union all

Union 合并结果集（去重），Union all(不去重)，例如有两个Select语句，他们结果集中字段个数相等（必须条件），通过Union可以将结果合并。

1. 子查询

使用子查询一定清楚子查询中结果产生是多个还是一个，一个的话用到关键字如：= > <, 多个的话：in any all，具体怎么写不详说了。

1. With rollup分组求和

With rollup估计这个大家很少见到用，但是这个也是报表中经常用到的，比如分组后，想要对分组之后结果产生一个小计或总计，可以用With rollup，具体怎么写后面详说。

1. 内置函数

大的方向上可以分为：数字函数、字符串函数、日期函数(包含格式转换)、条件判断函数(常用case when，if else，ifnull)、聚合函数(上面说过)、系统函数(用不到)。

例子：

     1、Where子句使用

      

 

     注意：

1. 避免使用单个条件查询sql语句，防止后续需求改动，重新去写sql；
2. 避免使用resultMap复杂类型结果集，完全可以使用动态sql标签代替，也可达到复用目的；
3. 针对于复杂类型结果集不要一味地去实体中加字段，破坏java面向对象思想，可以用map接受sql结果集结果返回业务层，其实数据访问层向业务层传递数据，是要通过独立的dto对象去传递。

     那么什么时候使用resultMap类型接受查询结果呢？

      

      针对于一对多关系的复杂类型推荐使用resultMap去接受结果集，避免二次查询多的一方。

     2、Group By分组

      

 

      3、Having分组过滤

      

      Having使用是对分组后数据在进行过滤。它必须放在group by之后，不能放在之前。最好是在group by 之前用where对数据先进行筛选，再去group by。

       4、Union和Union all

       

       Union all 经常用在报表中，它可以将多个结果集合并成一个，但在列上面要求相同。

      5、子查询

       1、子查询结果作为父查询条件，结果是多个还是一个，确认之后再使用关键字。   

       2、（=，>,  <）（in, not in, exists, any, <> any, all）     

       https://www.cnblogs.com/chiangchou/p/mysql-3.html

       6、With rollup分组求和

       SELECT coalesce(name, '总金额'),name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP

        

        https://www.cnblogs.com/Mr-Cxy/p/5923375.html

        7、内置函数

        https://www.cnblogs.com/noway-neway/p/5211401.html

报表列子：

        

SELECT
	T.unit_id,
	T.unit_name,
	CONCAT(T.xjl, '%') AS xjl,
	CONCAT(T.rwwcl, '%') AS rwwcl,
	ROUND(T.xjl/2 + rwwcl/2) AS pj 
FROM (
	SELECT  
		T2.unit_id,
		T2.unit_name,
		CASE WHEN T3.inspect_count IS NOT NULL AND T4.inspected_count IS NOT NULL AND T3.inspect_count >= T4.inspected_count THEN ROUND(T4.inspected_count*100/T3.inspect_count,2)
			 WHEN T3.inspect_count IS NOT NULL AND T4.inspected_count IS NOT NULL AND T4.inspected_count > T3.inspect_count THEN 100 ELSE 0 END AS xjl,
		CASE WHEN T5.task_count IS NOT NULL AND T6.finish_count IS NOT NULL AND T5.task_count >= T6.finish_count THEN ROUND(T6.finish_count*100/T5.task_count,2)
			 WHEN T5.task_count IS NOT NULL AND T6.finish_count IS NOT NULL AND T6.finish_count > T5.task_count THEN 100 ELSE 0 END AS rwwcl
	FROM ( 
				SELECT DISTINCT T1.unit_id 
				FROM acc_user T1,acc_role T2, acc_user_role T3 
				WHERE T1.user_id =T3.user_id AND T2.role_id =T3.role_id AND T2.role_code = 'unitSectionChief'
	)T1
	LEFT JOIN acc_unit T2 ON T1.unit_id = T2.unit_id
	LEFT JOIN (
		SELECT 	
				T1.unit_id,
				CASE WHEN T1.inspect_count IS NOT NULL THEN T1.inspect_count ELSE 0 END AS inspect_count
		FROM (
				SELECT  
					T1.unit_id,
					SUM(T1.check_interval) AS inspect_count
				FROM task_inspection_time_setting T1, acc_role T2, acc_user_role T3
				WHERE T1.excute_user_id =T3.user_id AND T2.role_id =T3.role_id AND T2.role_code = 'unitSectionChief'
				GROUP BY T1.unit_id
		) T1	
	) T3 ON T1.unit_id = T3.unit_id
	LEFT JOIN (
		SELECT
			T1.unit_id,
			CASE WHEN T1.inspected_count IS NOT NULL THEN T1.inspected_count ELSE 0 END AS inspected_count
		FROM (
				SELECT
						T2.unit_id,
						COUNT( * ) AS inspected_count
				FROM task_daily_inspection T1, acc_user T2, acc_role T3, acc_user_role T4
				WHERE T1.report_user =T2.user_id AND T2.user_id = T4.user_id AND T3.role_id =T4.role_id AND T3.role_code = 'unitSectionChief' AND DATE_FORMAT(T1.report_time,'%Y-%m') = '2019-07'
				GROUP BY T2.unit_id
		) T1
	) T4 ON  T1.unit_id =T4.unit_id
	LEFT JOIN (
		SELECT
			T1.unit_id,
			CASE WHEN T1.task_count IS NOT NULL THEN T1.task_count ELSE 0 END AS task_count
		FROM (
				SELECT
						T2.unit_id,
						COUNT( * ) AS task_count
				FROM task_task_info T1, acc_role T2, acc_user_role T3
				WHERE T1.process_user =T3.user_id AND T2.role_id =T3.role_id AND T2.role_code = 'unitSectionChief' AND DATE_FORMAT(T1.distribute_time,'%Y-%m') = '2019-07'
				GROUP BY T1.unit_id
		) T1 
	) T5 ON T1.unit_id = T5.unit_id
	LEFT JOIN (
		SELECT
				T1.unit_id,
				CASE WHEN T1.finish_count IS NOT NULL THEN T1.finish_count ELSE 0 END AS finish_count
		FROM (
				SELECT
						T2.unit_id,
						COUNT( * ) AS finish_count
				FROM task_task_info T1, acc_role T2, acc_user_role T3
				WHERE T1.process_user =T3.user_id AND T2.role_id =T3.role_id AND T2.role_code = 'unitSectionChief' AND T1.`status` IN ('3', '4', '5') AND DATE_FORMAT(T1.process_time,'%Y-%m') = '2019-07'
				GROUP BY T1.unit_id
		) T1 
	) T6 ON T1.unit_id = T6.unit_id
	WHERE T2.unit_id != '1'
)T