乾学院 2188 号会员
1 回帖  •  357 浏览  • 
待解决      ...

请教这段 SQL 如何改写成 SPL 更合理

  

SELECT
	date_format( d.date_id, '%m/%d' ) date_id,
	ifnull( d.rc_cb, 0 ) rc_cb,
	ifnull( d.zd_cb, 0 ) zd_cb,
	ifnull( d.zd_cb_cq, 0 ) zd_cb_cq,
	ifnull( d.gg_zyc, 0 ) gg_zyc,
	ifnull( d.kx, 0 ) kx,
	ifnull( d.yg_gh, 0 ) + ifnull( d.cq_xj, 0 ) + ifnull( d.prj_fjf, 0 ) AS qt,-- 其他
	ifnull( d.rc_cb, 0 ) + ifnull( d.zd_cb, 0 ) + ifnull( d.zd_cb_cq, 0 ) + ifnull( d.gg_zyc, 0 ) + ifnull( d.kx, 0 ) + ifnull( d.yg_gh, 0 ) + ifnull( d.cq_xj, 0 ) + ifnull( d.kxt, 0 )+ ifnull( d.prj_fjf, 0 ) AS hj,
	ifnull( d.kxt, 0 ) kxt 
	
FROM
	(
	SELECT
		b.DATE_ID,
		sum( CASE WHEN b1.prj_type IN ( 'CR' ) AND b1.prj_name LIKE '%储备%' THEN 1 ELSE 0 END ) zd_cb,
		sum( CASE WHEN b1.prj_type IN ( 'GP', 'RP' ) AND b1.prj_name LIKE '%被动储备%' THEN 1 ELSE 0 END ) zd_cb_cq,
		sum(
		CASE
				
				WHEN REPLACE ( b1.JOIN_DATE, '-', '' ) <= b.DATE_ID AND REPLACE ( b1.START_DATE, '-', '' ) > b.DATE_ID THEN
				1 ELSE 0 
			END 
			) rc_cb,
			sum( CASE WHEN b1.prj_type IN ( 'GP', 'RP' ) AND b1.prj_name LIKE '%公共资源池%' THEN 1 ELSE 0 END ) gg_zyc,
			sum( CASE WHEN b1.prj_type = 'I' THEN 1 ELSE 0 END ) kx,
			sum( CASE WHEN b1.prj_type IN ( 'SR', 'LR' ) AND b1.prj_name LIKE '%员工关怀%' THEN 1 ELSE 0 END ) yg_gh,
			sum( CASE WHEN b1.prj_type IN ( 'SR', 'LR' ) AND b1.prj_name LIKE '%长期休假%' THEN 1 ELSE 0 END ) cq_xj,
			sum( CASE WHEN b1.prj_type IN ( 'GP', 'RP' ) AND b1.prj_name LIKE '%可协调资源池%' THEN 1 ELSE 0 END ) kxt,
			sum(
			CASE
					
					WHEN b1.prj_type IN ( 'C', 'C1' ) 
					AND ((
							ifnull( b1.START_DATE, '' ) = '' 
							AND ifnull( b1.END_DATE, '' ) = '' 
							) 
						OR ( b1.END_DATE < b1.leave_date AND b1.END_DATE = b.DATE_ID )) THEN
						1 ELSE 0 
					END 
					) prj_fjf 
				FROM
					calendar_d b,
					(
					SELECT
						a.emp_no,
						a.join_date,
						a.leave_date,
						a.start_date,
						a.end_date,
						a1.prj_name,
						a1.prj_type,
						a2.parentNo bu 
					FROM
						emp_prj a
						LEFT JOIN prj_info a1 ON a.prj_no = a1.prj_no
						LEFT JOIN dept a2 ON a1.ss_dept_id = a2.deptno 
					WHERE
						((
								'${sdate}' <= REPLACE ( a.join_date, '-', '' ) AND '${edate}' >= REPLACE ( a.join_date, '-', '' ) 
								AND '${edate}' <= REPLACE ( a.leave_date, '-', '' )) OR ( '${sdate}' >= REPLACE ( a.join_date, '-', '' ) 
								AND '${edate}' <= REPLACE ( a.leave_date, '-', '' )) OR ( '${sdate}' >= REPLACE ( a.join_date, '-', '' ) 
								AND '${sdate}' <= REPLACE ( a.leave_date, '-', '' ) AND '${edate}' >= REPLACE ( a.leave_date, '-', '' )) 
							OR (
							'${sdate}' <= REPLACE ( a.join_date, '-', '' ) AND '${edate}' >= REPLACE ( a.leave_date, '-', '' )) 
						) 
					AND
					IF
						( '${buno}' = 'BG000', a2.parentNo LIKE 'BU%', a2.parentNo = '${buno}' ) 
					) b1 
				WHERE
					REPLACE ( b1.JOIN_DATE, '-', '' ) <= b.DATE_ID AND REPLACE ( b1.LEAVE_DATE, '-', '' ) >= b.DATE_ID 
					AND b.DATE_ID >= '${sdate}' 
					AND b.DATE_ID <= '${edate}' 
				AND
				IF
					( '${buno}' = 'BG000', b1.bu LIKE 'BU%', b1.bu = '${buno}' ) 
				GROUP BY
				b.DATE_ID 
	) d

涉及的 4 个表都不大，数据量如下：

      select count(*) from calendar_d  3675
      select count(*) from emp_prj     98159
      select count(*) from prj_info    11471
      select count(*) from dept        2051

其中 calendar_d 的主键是 DATE_ID。

但后面关联以后的结果集会比较大，现在查询的时间在 150 秒以上。请问如何使用集算器改写更合理？预期查询时间在 1 秒以内。