你的位置:首页 > 数据库

[数据库]分析函数改写自关联


数据库环境: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

通过分析函数的改写,可以减少表的扫描。以后如果遇到类似需求,不妨改成这样试试。

(本文完)