SQL分析

1.分页的时候为了加一个恶心的筛选，写了一个超级复杂的sql：

总结：

--因为要筛选id在某一个集合，但是集合数据又是需要两个连表查询sum值，因为一起连表会造成数据紊乱，所以

--1.通过left join 的方式通过id匹配成 id - num1 -num2 -num3的样式

--2.两个数据通过union的方式连接，比 and (in  or  in)的方式效率要高

--3.in在使用过程中  where 1=1 and (a in or a in)如果在条件允许的情况下可以置换成 where 1=1 and (a not in and a not in)

SELECT
	COUNT (*)
FROM
	BASE_AREA_PROJECT T
WHERE
	1 = 1
AND T .T_YEAR = 'year_2014'
AND T .DEL_FLAG = 0
AND (
--因为要筛选id在某一个集合，但是集合数据又是需要两个连表查询sum值，因为一起连表会造成数据紊乱，所以
--1.通过left join 的方式通过id匹配成 id - num1 -num2 -num3的样式
--2.两个数据通过union的方式连接，比 and (in  or  in)的方式效率要高
	T .PRO_ID IN (
		SELECT
			*
		FROM
			(
				SELECT
					A .pro_id
				FROM
					(
						SELECT
							i.pro_id,
							NVL (SUM(i.juesun_money), 0) AS juesuan,
							NVL (SUM(i.PAID_MONEY), 0) AS paid
						FROM
							base_school_item i,
							BASE_AREA_PROJECT P
						WHERE
							1 = 1
						AND i.pro_id = P .pro_id
						AND P .PRO_TYPE = 'gb_project_type_tj'
						AND (
							i.DELETE_FLAG = '0'
							OR i.DELETE_FLAG = '-1'
						)
						AND i.SHANGBAO_STATUS = 2
						AND i.pro_id IS NOT NULL
						GROUP BY
							i.PRO_ID
					) A
				WHERE
					A .paid >= A .juesuan
				OR A .juesuan - A .paid <= 0.5
			)
		UNION
			(
				SELECT
					A .pro_id
				FROM
					(
						SELECT
							A .pro_id,
							A .juesuan,
							b.money
						FROM
							(
								SELECT
									P .pro_id,
									NVL (SUM(i.juesun_money), 0) AS juesuan
								FROM
									base_school_item i,
									BASE_AREA_PROJECT P
								WHERE
									1 = 1
								AND i.PRO_ID = P .pro_id
								AND P .PRO_TYPE = 'gb_project_type_sbyq'
								AND (
									i.DELETE_FLAG = '0'
									OR i.DELETE_FLAG = '-1'
								)
								AND i.SHANGBAO_STATUS = 2
								AND i.pro_id IS NOT NULL
								GROUP BY
									P .PRO_ID
							) A
						LEFT JOIN (
							SELECT
								P .pro_id,
								NVL (SUM(b.money), 0) AS money
							FROM
								item_pay_records b,
								BASE_AREA_PROJECT P
							WHERE
								1 = 1
							AND b.pro_id = P .pro_id
							AND P .PRO_TYPE = 'gb_project_type_sbyq'
							GROUP BY
								P .PRO_ID
						) b ON A .pro_id = b.PRO_ID
					) A
				WHERE
					A .money >= A .juesuan
				OR A .juesuan - A .money <= 0.5
			)
	)
)
ORDER BY
	T .CRE_TIME DESC,
	T .PRO_ID

实际效果：从17s查询降低到6s左右，如果要继续优化的化：通过筛选条件去掉union查询，只需要一个就行

2.