資料庫環境:SQL SERVER 2005
我們會看到這樣的需求,查詢部門的員工資訊和對應的部門人數,要求在同一行上展示。
在沒有分析函式之前,遇到需求,只能通過員工表自關聯來實現。
我這裡就有一個類似的例子
SELECT COUNT(*) FROM dmrpout_03 LEFT JOIN ctlm1210 ctlm1210_id_cgroup ON dmrpout_03. id_cgroup = ctlm1210_id_cgroup.id_cgroup LEFT JOIN ctlm7161 ctlm7161_id_schtype ON dmrpout_03. id_schtype = ctlm7161_id_schtype .id_schtype LEFT JOIN ctlm5003 ctlm5003_id_proc ON dmrpout_03. id_proc = ctlm5003_id_proc.id_proc LEFT JOIN ctlm1003 ctlm1003_id_dept ON dmrpout_03. id_dept = ctlm1003_id_dept.id_dept LEFT JOIN ctlm5111 ctlm5111_id_wkct ON dmrpout_03. id_wkct = ctlm5111_id_wkct.id_wkct LEFT JOIN ctlm1007 ctlm1007_id_clerk ON dmrpout_03. id_clerk = ctlm1007_id_clerk.id_clerk LEFT JOIN ctlm3006 ctlm3006_id_stockreas ON dmrpout_03.id_stockreas = ctlm3006_id_stockreas.id_stockreas LEFT JOIN ctlm3004 ctlm3004_id_keeper ON dmrpout_03. id_keeper = ctlm3004_id_keeper.id_keeper LEFT JOIN ctlm3001 ctlm3001_id_wh ON dmrpout_03. id_wh = ctlm3001_id_wh.id_wh LEFT JOIN ctlm5112 ctlm5112_id_pline ON dmrpout_03. id_pline = ctlm5112_id_pline.id_pline LEFT JOIN ctlm3001 ctlm3001_id_prodwh ON dmrpout_03. id_prodwh = ctlm3001_id_prodwh.id_wh LEFT JOIN ctlm1005 ctlm1005_id_recorder ON dmrpout_03.id_recorder = ctlm1005_id_recorder.id_user LEFT JOIN ctlm3002 ctlm3002_id_loc ON dmrpout_03. id_loc = ctlm3002_id_loc.id_loc LEFT JOIN ctlm3002 ctlm3002_id_prodloc ON dmrpout_03. id_prodloc = ctlm3002_id_prodloc .id_loc LEFT JOIN v_ctlm2004 v_ctlm2004_id_uom ON dmrpout_03. id_uom = v_ctlm2004_id_uom.id_uom LEFT JOIN v_ctlm2004 v_ctlm2004_id_secuom ON dmrpout_03.id_secuom = v_ctlm2004_id_secuom.id_uom WHERE dmrpout_03.line_no = ( SELECT MIN( line_no) FROM dmrpout_03 b WHERE b .dmrpout_no = dmrpout_03.dmrpout_no AND b .fiscal_year = '2015' AND b .fiscal_period = '6' AND b .id_dept IN ( SELECT id_dept FROM ctlm1003 ctlm1003_id_dept WHERE ctlm1003_id_dept.name_dept LIKE '%生產%' ) AND b .id_wkct IN ( SELECT id_wkct FROM ctlm5111 ctlm5111_id_wkct WHERE ctlm5111_id_wkct.name_wkct LIKE '%儲運%' ) ) AND dmrpout_03 .fiscal_year = '2015' AND dmrpout_03 .fiscal_period = '6' AND ctlm1003_id_dept .name_dept LIKE '% 生產%' AND ctlm5111_id_wkct .name_wkct LIKE '% 儲運%'
這條SQL要執行4分多鐘才出結果。我們可以從2方面進行優化,第一,SQL裡有很多LEFT JOIN,
都是dmrpout_03 LEFT JOIN其它表,如果是一對一關係,則LEFT JOIN後面的表可以省略;
第二,將自連線改寫成分析函式。
改寫後的SQL如下,只耗時12S就出結果了。
SELECT COUNT (*) FROM ( SELECT dmrpout_03.line_no , MIN(line_no ) OVER ( PARTITION BY dmrpout_03.dmrpout_no ) AS minline_no FROM dmrpout_03 LEFT JOIN ctlm1003 ctlm1003_id_dept ON dmrpout_03.id_dept = ctlm1003_id_dept.id_dept LEFT JOIN ctlm5111 ctlm5111_id_wkct ON dmrpout_03.id_wkct = ctlm5111_id_wkct.id_wkct LEFT JOIN ctlm3002 ctlm3002_id_loc ON dmrpout_03.id_loc = ctlm3002_id_loc.id_loc LEFT JOIN ctlm3002 ctlm3002_id_prodloc ON dmrpout_03.id_prodloc = ctlm3002_id_prodloc.id_loc LEFT JOIN v_ctlm2004 v_ctlm2004_id_uom ON dmrpout_03.id_uom = v_ctlm2004_id_uom.id_uom LEFT JOIN v_ctlm2004 v_ctlm2004_id_secuom ON dmrpout_03.id_secuom = v_ctlm2004_id_secuom.id_uom WHERE dmrpout_03 .fiscal_year = '2015' AND dmrpout_03 .fiscal_period = '6' AND dmrpout_03 .id_dept IN ( SELECT id_dept FROM ctlm1003 ctlm1003_id_dept WHERE ctlm1003_id_dept .name_dept LIKE '%生產%' ) AND dmrpout_03 .id_wkct IN ( SELECT id_wkct FROM ctlm5111 ctlm5111_id_wkct WHERE ctlm5111_id_wkct .name_wkct LIKE '%儲運%' ) ) t WHERE t.line_no = minline_no
通過分析函式的改寫,可以減少表的掃描。以後如果遇到類似需求,不妨改成這樣試試。
(本文完)