sql 数据 查询 语句 优化

[齐_天_大_圣]

请帮忙看看，这条sql结构上哪里不好，还能继续优化的地方。谢了

 SELECT
	aa.PNAME AS NAME,
	aa.PRICE1 AS PRICE,
	aa.COU,
	aa.JE,
	aa.ZQJE,
	(aa.JE / bb.YYE * 100) AS YEEZB
FROM
	(
		SELECT
			a.PNAME,
			c.PRICE1,
			ifnull(
				sum(
					CASE
					WHEN b.ORCLASS IN (2, 3) THEN
						- COUNT
					ELSE
						COUNT
					END
				),
				0
			) AS COU,
			ifnull(
				sum(
					CASE
					WHEN b.ORCLASS IN (2, 3) THEN
						- (a.YMONEY - a.DIFFERENTMONEY)
					ELSE
						(a.YMONEY - a.DIFFERENTMONEY)
					END
				),
				0
			) AS JE,
			ifnull(
				sum(
					CASE
					WHEN b.ORCLASS IN (2, 3) THEN
						- (a.MONEY)
					ELSE
						(a.MONEY)
					END
				),
				0
			) AS ZQJE
		FROM
			kpartitmes_HISTORY a,
			bills_HISTORY b,
			products c
		WHERE
			a.BCODE = b.BCODE
		AND b.ORCLASS IN (1, 2, 3, 6)
		AND a.PCODE = c.PCODE
		AND c.CCLEASSID1 = '1011'
		AND date(a.WORKDATE) >= date('2017-05-05')
		AND date(a.WORKDATE) <= date('2017-05-07')
		GROUP BY
			a.PNAME
		UNION ALL
			SELECT
				d.PNAME,
				c.PRICE1,
				0 AS COU,
				ifnull(
					sum(
						CASE
						WHEN b.ORCLASS IN (2, 3) THEN
							- a.FYMONEY
						WHEN b.orclass IN (5) THEN
							0
						ELSE
							a.FYMONEY
						END
					),
					0
				) AS JE,
				ifnull(
					sum(
						CASE
						WHEN b.ORCLASS IN (2, 3) THEN
							- a.FMONEY
						ELSE
							a.FMONEY
						END
					),
					0
				) AS ZQJE
			FROM
				fujia_HISTORY a,
				bills_HISTORY b,
				products c,
				kpartitmes_HISTORY d
			WHERE
				a.BCODE = b.BCODE
			AND b.ORCLASS IN (1, 2, 3, 6)
			AND d.PCODE = c.PCODE
			AND a.PROGROUPID = d.PRINTORDERID
			AND d.BCODE = b.BCODE
			AND d.PCODE = a.PCODE
			AND date(b.WORKDATE) >= date('2017-05-05')
			AND date(b.WORKDATE) <= date('2017-05-07')
			AND (
				(
					a.VISADDPROD = '1'
					AND c.CCLEASSID1 = '1011'
				)
				OR (
					a.STATE = 1
					AND c.CCLEASSID1 = '1011'
				)
			)
			GROUP BY
				a.FNAME 
			UNION ALL
				SELECT
					CONCAT(a.PSNAME, 'T') AS PSNAME,
					c.PRICE1,
					ifnull(
						sum(
							CASE
							WHEN b.ORCLASS IN (2, 3) THEN
								- 1
							ELSE
								1
							END
						),
						0
					) AS COU,
					ifnull(
						sum(
							CASE
							WHEN b.ORCLASS IN (2, 3) THEN
								- (a.YMONEY)
							WHEN b.orclass IN (5) THEN
								0
							ELSE
								(a.YMONEY)
							END
						),
						0
					) AS JE,
					ifnull(
						sum(
							CASE
							WHEN b.ORCLASS IN (2, 3) THEN
								- (a.PRICE1)
							ELSE
								(a.PRICE1)
							END
						),
						0
					) AS ZQJE
				FROM
					productsublist_HISTORY a,
					bills_HISTORY b,
					products c
				WHERE
					a.BCODE = b.BCODE
				AND b.ORCLASS IN (1, 2, 3, 6)
				AND a.PCODE = c.PCODE
				AND c.CCLEASSID1 = '1011'
				AND date(b.WORKDATE) >= date('2017-05-05')
				AND date(b.WORKDATE) <= date('2017-05-07')
				GROUP BY
					a.PSNAME
	) aa,
	(
		SELECT
			sum(t.FJMONEY) AS YYE
		FROM
			(
				SELECT
					ifnull(
						sum(
							CASE
							WHEN a.orclass IN (1, 6) THEN
								b.FYMONEY
							ELSE
								- b.FYMONEY
							END
						),
						0
					) AS FJMONEY
				FROM
					bills_HISTORY a,
					fujia_HISTORY b
				WHERE
					a.BCODE = b.BCODE
				AND a.orclass IN (1, 6, 2, 3)
				AND b.state IN (1, 5)
				AND date(a.WORKDATE) >= date('2017-05-05')
				AND date(a.WORKDATE) <= date('2017-05-07')
				UNION ALL
					SELECT
						ifnull(
							sum(
								CASE
								WHEN a.ORCLASS IN (1, 6) THEN
									(
										YMONEY - DIFFERENTMONEY + YSERVICEFEE
									)
								ELSE
									- (
										YMONEY - DIFFERENTMONEY + YSERVICEFEE
									)
								END
							),
							0
						) AS FJMONEY
					FROM
						bills_HISTORY a
					WHERE
						a.ORCLASS IN (1, 2, 3, 6)
					AND date(a.WORKDATE) >= date('2017-05-05')
					AND date(a.WORKDATE) <= date('2017-05-07')
					UNION ALL
						SELECT
							ifnull(
								sum(
									CASE
									WHEN d.ORCLASS IN (2, 3) THEN
										a.Ymoney
									ELSE
										- a.Ymoney
									END
								),
								0
							) AS FJMONEY
						FROM
							kpartitmes_HISTORY a,
							l_products b,
							products c,
							bills_HISTORY d
						WHERE
							b.LINKED = 0
						AND b.ISMAINBUSINESS = 0
						AND b.id = CCLEASSID1
						AND c.PCODE = a.pcode
						AND d.ORCLASS IN (1, 2, 3, 6)
						AND a.BCODE = d.BCODE
						AND date(a.WORKDATE) >= date('2017-05-05')
						AND date(a.WORKDATE) <= date('2017-05-07')
			) t
	) bb
WHERE
	aa.COU > 0
OR aa.JE > 0

[碧水幽幽泉]

ifnull是mysql里面的函数。
oracle没有ifnull 但是有相应的替换函数 nvl 。

[碧水幽幽泉]

union all 代码有个共同点：存在bills_HISTORY
将表bills_HISTORY，条件ORCLASS IN (1, 2, 3, 6)，单独列出来写一段sql。
再对ORCLASS IN (1, 2, 3, 6)进行处理， CASE WHEN ORCLASS IN(2，3) THEN 'Y' ELSE 'N' END FLAG，
其他语句就可以对FLAG进行判断，CASE WHEN语句都可以换成DECODE.

[「已注销」]

AND date(a.WORKDATE) >= date('2017-05-05') AND date(a.WORKDATE) <= date('2017-05-07') 不要写这种东西，你这样除非你的列建了函数索引，否则用不上索引的

[齐_天_大_圣]

引用 2 楼 qq646748739 的回复:

union all 代码有个共同点：存在bills_HISTORY 将表bills_HISTORY，条件ORCLASS IN (1, 2, 3, 6)，单独列出来写一段sql。 再对ORCLASS IN (1, 2, 3, 6)进行处理， CASE WHEN ORCLASS IN(2，3) THEN 'Y' ELSE 'N' END FLAG， 其他语句就可以对FLAG进行判断，CASE WHEN语句都可以换成DECODE.

thanks