请问，这个sql还有tunning的余地吗？

[Morgan_ma]

要在sql server 2005里运行
SELECT SubGeo, PLine, LevelCode, Account, HQ, SUM(ISNULL(Nov1, 0)) AS Nov1, SUM(ISNULL(Dec1, 0)) AS Dec1, SUM(ISNULL(Jan1, 0)) AS Jan1, SUM(ISNULL(Feb1, 0)) AS Feb1, SUM(ISNULL(Mar1, 0)) AS Mar1, SUM(ISNULL(Apr1, 0)) AS Apr1, SUM(ISNULL(May1, 0)) AS May1, SUM(ISNULL(Jun1, 0)) AS Jun1, SUM(ISNULL(Jul1, 0)) AS Jul1, SUM(ISNULL(Aug1, 0)) AS Aug1, SUM(ISNULL(Sep1, 0)) AS Sep1, SUM(ISNULL(Oct1, 0)) AS Oct1, SUM(ISNULL(Nov2, 0)) AS Nov2, SUM(ISNULL(Dec2, 0)) AS Dec2, SUM(ISNULL(Jan2, 0)) AS Jan2, SUM(ISNULL(Feb2, 0)) AS Feb2, SUM(ISNULL(Mar2, 0)) AS Mar2, SUM(ISNULL(Apr2, 0)) AS Apr2, SUM(ISNULL(May2, 0)) AS May2, SUM(ISNULL(Jun2, 0)) AS Jun2, SUM(ISNULL(Jul2, 0)) AS Jul2, SUM(ISNULL(Aug2, 0)) AS Aug2, SUM(ISNULL(Sep2, 0)) AS Sep2, SUM(ISNULL(Oct2, 0)) AS Oct2, SUM(ISNULL(Nov3, 0)) AS Nov3, SUM(ISNULL(Dec3, 0)) AS Dec3, SUM(ISNULL(Jan3, 0)) AS Jan3, SUM(ISNULL(Feb3, 0)) AS Feb3, SUM(ISNULL(Mar3, 0)) AS Mar3, SUM(ISNULL(Apr3, 0)) AS Apr3, SUM(ISNULL(May3, 0)) AS May3, SUM(ISNULL(Jun3, 0)) AS Jun3, SUM(ISNULL(Jul3, 0)) AS Jul3, SUM(ISNULL(Aug3, 0)) AS Aug3, SUM(ISNULL(Sep3, 0)) AS Sep3, SUM(ISNULL(Oct3, 0)) AS Oct3
FROM (
SELECT S.[Description] AS SubGeo, Y.[Description] AS PLine, Y.LevelCode AS LevelCode, B.[Description] AS Account, B.HeadQuarter AS HQ,SUM(ISNULL(Nov, 0)) AS Nov1, SUM(ISNULL([Dec], 0)) AS Dec1, SUM(ISNULL(Jan, 0)) AS Jan1, SUM(ISNULL(Feb, 0)) AS Feb1, SUM(ISNULL(Mar, 0)) AS Mar1, SUM(ISNULL(Apr, 0)) AS Apr1, SUM(ISNULL(May, 0)) AS May1, SUM(ISNULL(Jun, 0)) AS Jun1, SUM(ISNULL(Jul, 0)) AS Jul1, SUM(ISNULL(Aug, 0)) AS Aug1, SUM(ISNULL(Sep, 0)) AS Sep1, SUM(ISNULL(Oct, 0)) AS Oct1,0 AS Nov2, 0 AS Dec2, 0 AS Jan2, 0 AS Feb2, 0 AS Mar2, 0 AS Apr2, 0 AS May2, 0 AS Jun2, 0 AS Jul2, 0 AS Aug2, 0 AS Sep2, 0 AS Oct2,0 AS Nov3, 0 AS Dec3, 0 AS Jan3, 0 AS Feb3, 0 AS Mar3, 0 AS Apr3, 0 AS May3, 0 AS Jun3, 0 AS Jul3, 0 AS Aug3, 0 AS Sep3, 0 AS Oct3
FROM MainDataV D, CategoriesT A, CategoriesT B, SubGeoT S, PLineT Y WHERE A.Category = D.Category AND A.ParentCode = B.Category AND Y.PLine = D.PLine AND S.SubGeo = D.SubGeo AND
D.SubGeo IN ('CDA','BR','ME','CACE','CO','PU','D-MCA','VE','AR','BO','CH','FL','PA','PE','D-SMCA','UR','D-LA','US','D-AM','AHQ','CHI','HONGKONG','INDIA','KOREA','AEC','INDONESIA','MALAYSIA','PHILLIPPINES','SINGAPORE','THAILAND','VIETNAM','D-SEA','AUS','NZ','D-SOP','TAIWAN','D-AAAR','JAPAN','D-AP','CIS','CZ','EEM','HU','POL','RSA','SL','FR','GE','AU','BE','DE','FI','D-GWE','NE','NO','SW','SWI','HPEU','PO','SP','D-IB','IT','AF','CY','GR','IS','MEA','SF','TU','IR','UK','D-UKAI','D-EU','D-WW') AND D.PLine IN ('DA','JP','KS','ML','TW','TX','TY','TZ','UK','UL','UN','D-GSOL','UO','UQ','UR','1N','2A','2N','5M','83','A5','AU','B7','C2','DE','DL','HQ','KN','M3','R6','T5','TT','UI','UJ','D-INKS','WS','2PP','5T','KP','MB','MC','MK','MQ','UB','UD','UF','UH','D-IAPS','MISC','FCG','IMGPRI','2G','2H','2T','52','6J','9G','E1','KV','KW','KX','ME','MF','MM','MN','TD','D-PSGCON','G6','D-APPC','VC','EB','EI','PP','UE','D-EDS','R6N','D-MSREV','6NN','7PN','LPN','LVN','D-OMSON','6N','7P','LP','LV','D-OMSO','24N','25N','6LN','D-TMSON','24','25','6L','D-TMSO','D-EDSOS','2B','2D','2Q','30','4x','6A','7T','8A','9C','AK','C5','DU','LY','MA','PQ','SB','ST','T2','T4','D-COMHW','27','DN','EX','PR','D-OMS','2PN','2P','D-HALO','D-OTHERIPG','OENT','6H','D-PCRV','2C','7F','9F','BO','D-CLBUS','US','UT','UU','AN','MP','TA','D-COMNO','UV','21','9J','BQ','D-PERAPP','8W','UP','MG','D-OTHER','D-EMPSYS','5X','9H','TB','D-CLWKS','NW','1X','23','2M','61','HA','TQ','TR','D-BCSYS','4U','MV','D-SERNTVAL','UZ','LA','SI','SY','TN','D-SERNT','LJ','LK','LL','LM','LN','D-SAN','1Y','3C','7A','LI','D-NLN','D-COMSYS','06N','4JN','4K','72N','79','7GN','91N','EAN','G4','G5','JNN','R4N','R7N','R8N','D-SUPN','UW','UY','06','4J','72','7G','91','EA','JN','R4','R7','R8','D-SUP','D-ITSER','2K','LH','D-SOFTWO','87','TE','TF','U3','D-SOFTWOV','26','8L','G1','D-SOFTW','32','33','8SR','EFR','G2R','G3R','THR','TKR','D-SOFTWMR','2E','2F','32N','33N','8S','D3','EF','G2','G3','TH','TK','U4','D-SOFTWM','UX','VS') AND (D.Category IN ('DN','2K','2L','2S','2I','2M','2N','2O','DM','2U','2P','2Q','2R','2J','2H','2V','HH','D-AM HQ CMEs','54','BD','38','IM','MP','BF','42','BE','50','48','03','14','GP','9C','BX','49','CS','EE','HJ','D-AP HQ CMEs','GQ','06','09','73','D-EU HQ NEPs','KG','07','08','BN','HI','76','13','EV','XW','D-EU HQ SPs','83','D-CMEs','15','LD','YA','XV','FT','HO','ZM','LH','FR','JA','HB','BZ','2T','DA','FS','FI','YO','FA','D-AM HQ FSI','90','99','BL','61','CF','87','GK','DO','AI','AC','AJ','AN','BB','BC','BM','88','9A','UE','CZ','DE','05','FW','D-EU HQ FSI','D-Financial Svcs','97','80','65','EU','40','21','82','81','BR','01','EP','CH','GY','68','ED','D-AM HQ MFG','DL','62','EY','GS','GR','47','92','95','IC','96','D-AP HQ MFG','16','18','41','BJ','29','30','YT','32','91','FC','34','FM','AO','43','19','FQ','27','31','DJ','DK','D-EU HQ MFG','D-Manufacturing','IH','23','BW','CE','CT','AE','CP','25','AG','HG','GO','HA','IY','D-PSs','D-Corporate Accounts','ZB','ZC','ZD','ZE','1P','ZF','1T','2D','1U','ZS','ZH','ZJ','ZK','1Q','1R','1S','2F','2G','1X','1Z','1W','1Y','2E','ZX','2A','ZN','ZO','KB','ZG','ZQ','1M','ZR','1N','ZP','ZI','ZT','ZU','JR','ZL','1L','ZA','ZW','1O','ZY','ZV','ZZ','1A','1B','YZ','1E','1F','1C','1G','1H','1I','1J','1K','CD','FU','HE','HT','FD','HU','FB','EA','EC','GF','LN','LW','EM','GU','LO','LQ','12','LR','LS','LT','LU','GC','MA','DY','CN','IF','MD','GA','GB','KK','IK','KL','IL','AF','GI','ME','MF','MG','MH','MI','MJ','2C','36','MK','ML','MM','MN','IN','MO','XQ','MQ','MR','BH','MT','60','MU','IO','MW','IP','MX','MY','EK','MZ','IQ','OB','OC','OD','OE','IX','OF','KF','OG','2B','IR','OH','OI','OJ','OK','OL','IS','IT','GE','ON','OP','EB','OQ','OR','OS','OT','EF','OU','OW','IU','OY','OZ','PA','PT','PB','IV','LV','PC','PD','PE','PF','PG','PH','PS','PJ','PK','PI','PM','PN','PO','PQ','PR','XY','AT','AU','IB','GT','IE','ID','9D','DD','XZ','DH','86','QD','QP','RE','YW','RL','RM','UM','SA','LA','SE','SI','SL','SW','SX','SY','SZ','TH','TJ','TS','TX','TY','UB','UD','UJ','UK','UP','UQ','UU','VZ','YV','VD','VE','VF','VG','QO','VM','VN','YU','RA','YX','VP','VR','VS','XU','WB','WT','WW'

[Morgan_ma]

SELECT S.[Description] AS SubGeo, Y.[Description] AS PLine, Y.LevelCode AS LevelCode, B.[Description] AS Account, B.HeadQuarter AS HQ,0 AS Nov2, 0 AS Dec2, 0 AS Jan2, 0 AS Feb2, 0 AS Mar2, 0 AS Apr2, 0 AS May2, 0 AS Jun2, 0 AS Jul2, 0 AS Aug2, 0 AS Sep2, 0 AS Oct2,SUM(ISNULL(Nov, 0)) AS Nov1, SUM(ISNULL([Dec], 0)) AS Dec1, SUM(ISNULL(Jan, 0)) AS Jan1, SUM(ISNULL(Feb, 0)) AS Feb1, SUM(ISNULL(Mar, 0)) AS Mar1, SUM(ISNULL(Apr, 0)) AS Apr1, SUM(ISNULL(May, 0)) AS May1, SUM(ISNULL(Jun, 0)) AS Jun1, SUM(ISNULL(Jul, 0)) AS Jul1, SUM(ISNULL(Aug, 0)) AS Aug1, SUM(ISNULL(Sep, 0)) AS Sep1, SUM(ISNULL(Oct, 0)) AS Oct1,0 AS Nov3, 0 AS Dec3, 0 AS Jan3, 0 AS Feb3, 0 AS Mar3, 0 AS Apr3, 0 AS May3, 0 AS Jun3, 0 AS Jul3, 0 AS Aug3, 0 AS Sep3, 0 AS Oct3
FROM MainDataV D, CategoriesT A, CategoriesT B, SubGeoT S, PLineT Y WHERE A.Category = D.Category AND A.ParentCode = B.Category AND Y.PLine = D.PLine AND S.SubGeo = D.SubGeo AND
D.SubGeo IN ('CDA','BR','ME','CACE','CO','PU','D-MCA','VE','AR','BO','CH','FL','PA','PE','D-SMCA','UR','D-LA','US','D-AM','AHQ','CHI','HONGKONG','INDIA','KOREA','AEC','INDONESIA','MALAYSIA','PHILLIPPINES','SINGAPORE','THAILAND','VIETNAM','D-SEA','AUS','NZ','D-SOP','TAIWAN','D-AAAR','JAPAN','D-AP','CIS','CZ','EEM','HU','POL','RSA','SL','FR','GE','AU','BE','DE','FI','D-GWE','NE','NO','SW','SWI','HPEU','PO','SP','D-IB','IT','AF','CY','GR','IS','MEA','SF','TU','IR','UK','D-UKAI','D-EU','D-WW') AND D.PLine IN ('DA','JP','KS','ML','TW','TX','TY','TZ','UK','UL','UN','D-GSOL','UO','UQ','UR','1N','2A','2N','5M','83','A5','AU','B7','C2','DE','DL','HQ','KN','M3','R6','T5','TT','UI','UJ','D-INKS','WS','2PP','5T','KP','MB','MC','MK','MQ','UB','UD','UF','UH','D-IAPS','MISC','FCG','IMGPRI','2G','2H','2T','52','6J','9G','E1','KV','KW','KX','ME','MF','MM','MN','TD','D-PSGCON','G6','D-APPC','VC','EB','EI','PP','UE','D-EDS','R6N','D-MSREV','6NN','7PN','LPN','LVN','D-OMSON','6N','7P','LP','LV','D-OMSO','24N','25N','6LN','D-TMSON','24','25','6L','D-TMSO','D-EDSOS','2B','2D','2Q','30','4x','6A','7T','8A','9C','AK','C5','DU','LY','MA','PQ','SB','ST','T2','T4','D-COMHW','27','DN','EX','PR','D-OMS','2PN','2P','D-HALO','D-OTHERIPG','OENT','6H','D-PCRV','2C','7F','9F','BO','D-CLBUS','US','UT','UU','AN','MP','TA','D-COMNO','UV','21','9J','BQ','D-PERAPP','8W','UP','MG','D-OTHER','D-EMPSYS','5X','9H','TB','D-CLWKS','NW','1X','23','2M','61','HA','TQ','TR','D-BCSYS','4U','MV','D-SERNTVAL','UZ','LA','SI','SY','TN','D-SERNT','LJ','LK','LL','LM','LN','D-SAN','1Y','3C','7A','LI','D-NLN','D-COMSYS','06N','4JN','4K','72N','79','7GN','91N','EAN','G4','G5','JNN','R4N','R7N','R8N','D-SUPN','UW','UY','06','4J','72','7G','91','EA','JN','R4','R7','R8','D-SUP','D-ITSER','2K','LH','D-SOFTWO','87','TE','TF','U3','D-SOFTWOV','26','8L','G1','D-SOFTW','32','33','8SR','EFR','G2R','G3R','THR','TKR','D-SOFTWMR','2E','2F','32N','33N','8S','D3','EF','G2','G3','TH','TK','U4','D-SOFTWM','UX','VS') AND (D.Category IN ('DN','2K','2L','2S','2I','2M','2N','2O','DM','2U','2P','2Q','2R','2J','2H','2V','HH','D-AM HQ CMEs','54','BD','38','IM','MP','BF','42','BE','50','48','03','14','GP','9C','BX','49','CS','EE','HJ','D-AP HQ CMEs','GQ','06','09','73','D-EU HQ NEPs','KG','07','08','BN','HI','76','13','EV','XW','D-EU HQ SPs','83','D-CMEs','15','LD','YA','XV','FT','HO','ZM','LH','FR','JA','HB','BZ','2T','DA','FS','FI','YO','FA','D-AM HQ FSI','90','99','BL','61','CF','87','GK','DO','AI','AC','AJ','AN','BB','BC','BM','88','9A','UE','CZ','DE','05','FW','D-EU HQ FSI','D-Financial Svcs','97','80','65','EU','40','21','82','81','BR','01','EP','CH','GY','68','ED','D-AM HQ MFG','DL','62','EY','GS','GR','47','92','95','IC','96','D-AP HQ MFG','16','18','41','BJ','29','30','YT','32','91','FC','34','FM','AO','43','19','FQ','27','31','DJ','DK','D-EU HQ MFG','D-Manufacturing','IH','23','BW','CE','CT','AE','CP','25','AG','HG','GO','HA','IY','D-PSs','D-Corporate Accounts','ZB','ZC','ZD','ZE','1P','ZF','1T','2D','1U','ZS','ZH','ZJ','ZK','1Q','1R','1S','2F','2G','1X','1Z','1W','1Y','2E','ZX','2A','ZN','ZO','KB','ZG','ZQ','1M','ZR','1N','ZP','ZI','ZT','ZU','JR','ZL','1L','ZA','ZW','1O','ZY','ZV','ZZ','1A','1B','YZ','1E','1F','1C','1G','1H','1I','1J','1K','CD','FU','HE','HT','FD','HU','FB','EA','EC','GF','LN','LW','EM','GU','LO','LQ','12','LR','LS','LT','LU','GC','MA','DY','CN','IF','MD','GA','GB','KK','IK','KL','IL','AF','GI','ME','MF','MG','MH','MI','MJ','2C','36','MK','ML','MM','MN','IN','MO','XQ','MQ','MR','BH','MT','60','MU','IO','MW','IP','MX','MY','EK','MZ','IQ','OB','OC','OD','OE','IX','OF','KF','OG','2B','IR','OH','OI','OJ','OK','OL','IS','IT','GE','ON','OP','EB','OQ','OR','OS','OT','EF','OU','OW','IU','OY','OZ','PA','PT','PB','IV','LV','PC','PD','PE','PF','PG','PH','PS','PJ','PK','PI','PM','PN','PO','PQ','PR','XY','AT','AU','IB','GT','IE','ID','9D','DD','XZ','DH','86','QD','QP','RE','YW','RL','RM','UM','SA','LA','SE','SI','SL','SW','SX','SY','SZ','TH','TJ','TS','TX','TY','UB','UD','UJ','UK','UP','UQ','UU','VZ','YV','VD','VE','VF','VG','QO','VM','VN','YU','RA','YX','VP','VR','VS','XU','WB','WT','WW','WZ','XA','XG','XM','XN','QA','QB','QC','QE','QF','QG','QH','QI','QJ','QK','QL','QM','QN','QQ','QR','QS','QT','QU','QV','QW','QX','QY','RB','RC','RD','RF','RG','RH','RI','RJ','RN','RO','RQ','RR','RS','RT','RU','RV','RW','RX','RY','RZ','SB','SD','SF','SG','SH','SJ','SK','SM','SN','SO','SP','SQ','SR','SS','ST','SU','SV','TA','TC','TD','TE','TF','TG','TI','TK','TL','TM','TN','TO','TP','TQ','TR','TT','TV','TZ','UA','UC','UF','UG','UH','UI','UL','UN','UO','UR','US','UT','UV','VW','VX','VY','VA','VB','VC','VH','VJ','VK','VL','VO','VQ','VT','VU','VV','XR','XS','XT','WA','WC','WD','WE','WG','WH','WI','WJ','WK','WL','WM','WN','WO','WP','WQ','WR','WU','WV','WX','WY','XB','XC','XD','XE','XF','XH','XI','XJ','XK','XL','XP','37','44','HQ','EO','YP','JD','FL','FK','IZ','YM','FN','HR','YQ','LL','YJ','HV','FO','HW','YB','JB','JJ','YS','YC','GM','HY','MV','LC','LK','HP','YD','LG','HC','YE','HN','JG','LI','JK','HX','LE','YF','85','YG','YH','HL','17','IA','LJ','FH','YR','LF','72','YI','LM','JI','JH','HZ','XO','KX','IJ','KI','KM','KN','KO','KP','YL','AY','KQ','67','KR','KS','GW','KT','GX','KU','YN','KW','YK','D-AM IPG-GLOBAL','KH','AW','YY','PY','LB','D-EU IPG-GLOBAL') OR B.Category IN ('DN','2K','2L','2S','2I','2M','2N','2O','DM','2U','2P','2Q','2R','2J','2H','2V','HH','D-AM HQ CMEs','54','BD','38','IM','MP','BF','42','BE','50','48','03','14','GP','9C','BX','49','CS','EE','HJ','D-AP HQ CMEs','GQ','06','09','73','D-EU HQ NEPs','KG','07','08','BN','HI','76','13','EV','XW','D-EU HQ SPs','83','D-CMEs','15','LD','YA','XV','FT','HO','ZM','LH','FR','JA','HB','BZ','2T','DA','FS','FI','YO','FA','D-AM HQ FSI','90','99','BL','61','CF','87','GK','DO','AI','AC','AJ','AN','BB','BC','BM','88','9A','UE','CZ','DE','05','FW','D-EU HQ FSI','D-Financial Svcs','97','80','65','EU','40','21','82','81','BR','01','EP','CH','GY','68','ED','D-AM HQ MFG','DL','62','EY','GS','GR','47','92','95','IC','96','D-AP HQ MFG','16','18','41','BJ','29','30','YT','32','91','FC','34','FM','AO','43','19','FQ','27','31','DJ','DK','D-EU HQ MFG','D-Manufacturing','IH','23','BW','CE','CT','AE','CP','25','AG','HG','GO','HA','IY','D-PSs','D-Corporate Accounts','ZB','ZC','ZD','ZE','1P','ZF','1T','2D','1U','ZS','ZH','ZJ','ZK','1Q','1R','1S','2F','2G','1X','1Z','1W','1Y','2E','ZX','2A','ZN','ZO','KB','ZG','ZQ','1M','ZR','1N','ZP','ZI','ZT','ZU','JR','ZL','1L','ZA','ZW','1O','ZY','ZV','ZZ','1A','1B','YZ','1E','1F','1C','1G','1H','1I','1J','1K','CD','FU','HE','HT','FD','HU','FB','EA','EC','GF','LN','LW','EM','GU','LO','LQ','12','LR','LS','LT','LU','GC','MA','DY','CN','IF','MD','GA','GB','KK','IK','KL','IL','AF','GI','ME','MF','MG','MH','MI','MJ','2C','36','MK','ML','MM','MN','IN','MO','XQ','MQ','MR','BH','MT','60','MU','IO','MW','IP','MX','MY','EK','MZ','IQ','OB','OC','OD','OE','IX','OF','KF','OG','2B','IR','OH','OI','OJ','OK','OL','IS','IT','GE','ON','OP','EB','OQ','OR','OS','OT','EF','OU','OW','IU','OY','OZ','PA','PT','PB','IV','LV','PC','PD','PE','PF','PG','PH','PS','PJ','PK','PI','PM','PN','PO','PQ','PR','XY','AT','AU','IB','GT','IE','ID','9D','DD','XZ','DH','86','QD','QP','RE','YW','RL','RM','UM','SA','LA','SE','SI','SL','SW','SX','SY','SZ','TH','TJ','TS','TX','TY','UB','UD','UJ','UK','UP','UQ','UU','VZ','YV','VD','VE','VF','VG','QO','VM','VN','YU','RA','YX','VP','VR','VS','XU','WB','WT','WW','WZ','XA','XG','XM','XN','QA','QB','QC','QE','QF','QG','QH','QI','QJ','QK','QL','QM','QN','QQ','QR','QS','QT','QU','QV','QW','QX','QY','RB','RC','RD','RF','RG','RH','RI','RJ','RN','RO','RQ','RR','RS','RT','RU','RV','RW','RX','RY','RZ','SB','SD','SF','SG','SH','SJ','SK','SM','SN','SO','SP','SQ','SR','SS','ST','SU','SV','TA','TC','TD','TE','TF','TG','TI','TK','TL','TM','TN','TO','TP','TQ','TR','TT','TV','TZ','UA','UC','UF','UG','UH','UI','UL','UN','UO','UR','US','UT','UV','VW','VX','VY','VA','VB','VC','VH','VJ','VK','VL','VO','VQ','VT','VU','VV','XR','XS','XT','WA','WC','WD','WE','WG','WH','WI','WJ','WK','WL','WM','WN','WO','WP','WQ','WR','WU','WV','WX','WY','XB','XC','XD','XE','XF','XH','XI','XJ','XK','XL','XP','37','44','HQ','EO','YP','JD','FL','FK','IZ','YM','FN','HR','YQ','LL','YJ','HV','FO','HW','YB','JB','JJ','YS','YC','GM','HY','MV','LC','LK','HP','YD','LG','HC','YE','HN','JG','LI','JK','HX','LE','YF','85','YG','YH','HL','17','IA','LJ','FH','YR','LF','72','YI','LM','JI','JH','HZ','XO','KX','IJ','KI','KM','KN','KO','KP','YL','AY','KQ','67','KR','KS','GW','KT','GX','KU','YN','KW','YK','D-AM IPG-GLOBAL','KH','AW','YY','PY','LB','D-EU IPG-GLOBAL'))

[Morgan_ma]

AND (DSource IN('I') OR DSource IN ('I') OR DSource IN ('I', 'I')) AND DType = 'A' GROUP BY S.[Description], Y.[Description], Y.LevelCode, B.[Description], B.HeadQuarter
UNION ALL
SELECT S.[Description] AS SubGeo, Y.[Description] AS PLine, Y.LevelCode AS LevelCode, B.[Description] AS Account, B.HeadQuarter AS HQ,0 AS Nov2, 0 AS Dec2, 0 AS Jan2, 0 AS Feb2, 0 AS Mar2, 0 AS Apr2, 0 AS May2, 0 AS Jun2, 0 AS Jul2, 0 AS Aug2, 0 AS Sep2, 0 AS Oct2,0 AS Nov3, 0 AS Dec3, 0 AS Jan3, 0 AS Feb3, 0 AS Mar3, 0 AS Apr3, 0 AS May3, 0 AS Jun3, 0 AS Jul3, 0 AS Aug3, 0 AS Sep3, 0 AS Oct3,SUM(ISNULL(Nov, 0)) AS Nov1, SUM(ISNULL([Dec], 0)) AS Dec1, SUM(ISNULL(Jan, 0)) AS Jan1, SUM(ISNULL(Feb, 0)) AS Feb1, SUM(ISNULL(Mar, 0)) AS Mar1, SUM(ISNULL(Apr, 0)) AS Apr1, SUM(ISNULL(May, 0)) AS May1, SUM(ISNULL(Jun, 0)) AS Jun1, SUM(ISNULL(Jul, 0)) AS Jul1, SUM(ISNULL(Aug, 0)) AS Aug1, SUM(ISNULL(Sep, 0)) AS Sep1, SUM(ISNULL(Oct, 0)) AS Oct1
FROM MainDataV D, CategoriesT A, CategoriesT B, SubGeoT S, PLineT Y WHERE A.Category = D.Category AND A.ParentCode = B.Category AND Y.PLine = D.PLine AND S.SubGeo = D.SubGeo AND
D.SubGeo IN ('CDA','BR','ME','CACE','CO','PU','D-MCA','VE','AR','BO','CH','FL','PA','PE','D-SMCA','UR','D-LA','US','D-AM','AHQ','CHI','HONGKONG','INDIA','KOREA','AEC','INDONESIA','MALAYSIA','PHILLIPPINES','SINGAPORE','THAILAND','VIETNAM','D-SEA','AUS','NZ','D-SOP','TAIWAN','D-AAAR','JAPAN','D-AP','CIS','CZ','EEM','HU','POL','RSA','SL','FR','GE','AU','BE','DE','FI','D-GWE','NE','NO','SW','SWI','HPEU','PO','SP','D-IB','IT','AF','CY','GR','IS','MEA','SF','TU','IR','UK','D-UKAI','D-EU','D-WW') AND D.PLine IN ('DA','JP','KS','ML','TW','TX','TY','TZ','UK','UL','UN','D-GSOL','UO','UQ','UR','1N','2A','2N','5M','83','A5','AU','B7','C2','DE','DL','HQ','KN','M3','R6','T5','TT','UI','UJ','D-INKS','WS','2PP','5T','KP','MB','MC','MK','MQ','UB','UD','UF','UH','D-IAPS','MISC','FCG','IMGPRI','2G','2H','2T','52','6J','9G','E1','KV','KW','KX','ME','MF','MM','MN','TD','D-PSGCON','G6','D-APPC','VC','EB','EI','PP','UE','D-EDS','R6N','D-MSREV','6NN','7PN','LPN','LVN','D-OMSON','6N','7P','LP','LV','D-OMSO','24N','25N','6LN','D-TMSON','24','25','6L','D-TMSO','D-EDSOS','2B','2D','2Q','30','4x','6A','7T','8A','9C','AK','C5','DU','LY','MA','PQ','SB','ST','T2','T4','D-COMHW','27','DN','EX','PR','D-OMS','2PN','2P','D-HALO','D-OTHERIPG','OENT','6H','D-PCRV','2C','7F','9F','BO','D-CLBUS','US','UT','UU','AN','MP','TA','D-COMNO','UV','21','9J','BQ','D-PERAPP','8W','UP','MG','D-OTHER','D-EMPSYS','5X','9H','TB','D-CLWKS','NW','1X','23','2M','61','HA','TQ','TR','D-BCSYS','4U','MV','D-SERNTVAL','UZ','LA','SI','SY','TN','D-SERNT','LJ','LK','LL','LM','LN','D-SAN','1Y','3C','7A','LI','D-NLN','D-COMSYS','06N','4JN','4K','72N','79','7GN','91N','EAN','G4','G5','JNN','R4N','R7N','R8N','D-SUPN','UW','UY','06','4J','72','7G','91','EA','JN','R4','R7','R8','D-SUP','D-ITSER','2K','LH','D-SOFTWO','87','TE','TF','U3','D-SOFTWOV','26','8L','G1','D-SOFTW','32','33','8SR','EFR','G2R','G3R','THR','TKR','D-SOFTWMR','2E','2F','32N','33N','8S','D3','EF','G2','G3','TH','TK','U4','D-SOFTWM','UX','VS') AND (D.Category IN ('DN','2K','2L','2S','2I','2M','2N','2O','DM','2U','2P','2Q','2R','2J','2H','2V','HH','D-AM HQ CMEs','54','BD','38','IM','MP','BF','42','BE','50','48','03','14','GP','9C','BX','49','CS','EE','HJ','D-AP HQ CMEs','GQ','06','09','73','D-EU HQ NEPs','KG','07','08','BN','HI','76','13','EV','XW','D-EU HQ SPs','83','D-CMEs','15','LD','YA','XV','FT','HO','ZM','LH','FR','JA','HB','BZ','2T','DA','FS','FI','YO','FA','D-AM HQ FSI','90','99','BL','61','CF','87','GK','DO','AI','AC','AJ','AN','BB','BC','BM','88','9A','UE','CZ','DE','05','FW','D-EU HQ FSI','D-Financial Svcs','97','80','65','EU','40','21','82','81','BR','01','EP','CH','GY','68','ED','D-AM HQ MFG','DL','62','EY','GS','GR','47','92','95','IC','96','D-AP HQ MFG','16','18','41','BJ','29','30','YT','32','91','FC','34','FM','AO','43','19','FQ','27','31','DJ','DK','D-EU HQ MFG','D-Manufacturing','IH','23','BW','CE','CT','AE','CP','25','AG','HG','GO','HA','IY','D-PSs','D-Corporate Accounts','ZB','ZC','ZD','ZE','1P','ZF','1T','2D','1U','ZS','ZH','ZJ','ZK','1Q','1R','1S','2F','2G','1X','1Z','1W','1Y','2E','ZX','2A','ZN','ZO','KB','ZG','ZQ','1M','ZR','1N','ZP','ZI','ZT','ZU','JR','ZL','1L','ZA','ZW','1O','ZY','ZV','ZZ','1A','1B','YZ','1E','1F','1C','1G','1H','1I','1J','1K','CD','FU','HE','HT','FD','HU','FB','EA','EC','GF','LN','LW','EM','GU','LO','LQ','12','LR','LS','LT','LU','GC','MA','DY','CN','IF','MD','GA','GB','KK','IK','KL','IL','AF','GI','ME','MF','MG','MH','MI','MJ','2C','36','MK','ML','MM','MN','IN','MO','XQ','MQ','MR','BH','MT','60','MU','IO','MW','IP','MX','MY','EK','MZ','IQ','OB','OC','OD','OE','IX','OF','KF','OG','2B','IR','OH','OI','OJ','OK','OL','IS','IT','GE','ON','OP','EB','OQ','OR','OS','OT','EF','OU','OW','IU','OY','OZ','PA','PT','PB','IV','LV','PC','PD','PE','PF','PG','PH','PS','PJ','PK','PI','PM','PN','PO','PQ','PR','XY','AT','AU','IB','GT','IE','ID','9D','DD','XZ','DH','86','QD','QP','RE','YW','RL','RM','UM','SA','LA','SE','SI','SL','SW','SX','SY','SZ','TH','TJ','TS','TX','TY','UB','UD','UJ','UK','UP','UQ','UU','VZ','YV','VD','VE','VF','VG','QO','VM','VN','YU','RA','YX','VP','VR','VS','XU','WB','WT','WW','WZ','XA','XG','XM','XN','QA','QB','QC','QE','QF','QG','QH','QI','QJ','QK','QL','QM','QN','QQ','QR','QS','QT','QU','QV','QW','QX','QY','RB','RC','RD','RF','RG','RH','RI','RJ','RN','RO','RQ','RR','RS','RT','RU','RV','RW','RX','RY','RZ','SB','SD','SF','SG','SH','SJ','SK','SM','SN','SO','SP','SQ','SR','SS','ST','SU','SV','TA','TC','TD','TE','TF','TG','TI','TK','TL','TM','TN','TO','TP','TQ','TR','TT','TV','TZ','UA','UC','UF','UG','UH','UI','UL','UN','UO','UR','US','UT','UV','VW','VX','VY','VA','VB','VC','VH','VJ','VK','VL','VO','VQ','VT','VU','VV','XR','XS','XT','WA','WC','WD','WE','WG','WH','WI','WJ','WK','WL','WM','WN','WO','WP','WQ','WR','WU','WV','WX','WY','XB','XC','XD','XE','XF','XH','XI','XJ','XK','XL','XP','37','44','HQ','EO','YP','JD','FL','FK','IZ','YM','FN','HR','YQ','LL','YJ','HV','FO','HW','YB','JB','JJ','YS','YC','GM','HY','MV','LC','LK','HP','YD','LG','HC','YE','HN','JG','LI','JK','HX','LE','YF','85','YG','YH','HL','17','IA','LJ','FH','YR','LF','72','YI','LM','JI','JH','HZ','XO','KX','IJ','KI','KM','KN','KO','KP','YL','AY','KQ','67','KR','KS','GW','KT','GX','KU','YN','KW','YK','D-AM IPG-GLOBAL','KH','AW','YY','PY','LB','D-EU IPG-GLOBAL') OR B.Category IN ('DN','2K','2L','2S','2I','2M','2N','2O','DM','2U','2P','2Q','2R','2J','2H','2V','HH','D-AM HQ CMEs','54','BD','38','IM','MP','BF','42','BE','50','48','03','14','GP','9C','BX','49','CS','EE','HJ','D-AP HQ CMEs','GQ','06','09','73','D-EU HQ NEPs','KG','07','08','BN','HI','76','13','EV','XW','D-EU HQ SPs','83','D-CMEs','15','LD','YA','XV','FT','HO','ZM','LH','FR','JA','HB','BZ','2T','DA','FS','FI','YO','FA','D-AM HQ FSI','90','99','BL','61','CF','87','GK','DO','AI','AC','AJ','AN','BB','BC','BM','88','9A','UE','CZ','DE','05','FW','D-EU HQ FSI','D-Financial Svcs','97','80','65','EU','40','21','82','81','BR','01','EP','CH','GY','68','ED','D-AM HQ MFG','DL','62','EY','GS','GR','47','92','95','IC','96','D-AP HQ MFG','16','18','41','BJ','29','30','YT','32','91','FC','34','FM','AO','43','19','FQ','27','31','DJ','DK','D-EU HQ MFG','D-Manufacturing','IH','23','BW','CE','CT','AE','CP','25','AG','HG','GO','HA','IY','D-PSs','D-Corporate Accounts','ZB','ZC','ZD','ZE','1P','ZF','1T','2D','1U','ZS','ZH','ZJ','ZK','1Q','1R','1S','2F','2G','1X','1Z','1W','1Y','2E','ZX','2A','ZN','ZO','KB','ZG','ZQ','1M','ZR','1N','ZP','ZI','ZT','ZU','JR','ZL','1L','ZA','ZW','1O','ZY','ZV','ZZ','1A','1B','YZ','1E','1F','1C','1G','1H','1I','1J','1K','CD','FU','HE','HT','FD','HU','FB','EA','EC','GF','LN','LW','EM','GU','LO','LQ','12','LR','LS','LT','LU','GC','MA','DY','CN','IF','MD','GA','GB','KK','IK','KL','IL','AF','GI','ME','MF','MG','MH','MI','MJ','2C','36','MK','ML','MM','MN','IN','MO','XQ','MQ','MR','BH','MT','60','MU','IO','MW','IP','MX','MY','EK','MZ','IQ','OB','OC','OD','OE','IX','OF','KF','OG','2B','IR','OH','OI','OJ','OK','OL','IS','IT','GE','ON','OP','EB','OQ','OR','OS','OT','EF','OU','OW','IU','OY','OZ','PA','PT','PB','IV','LV','PC','PD','PE','PF','PG','PH','PS','PJ','PK','PI','PM','PN','PO','PQ','PR','XY','AT','AU','IB','GT','IE','ID','9D','DD','XZ','DH','86','QD','QP','RE','YW','RL','RM','UM','SA','LA','SE','SI','SL','SW','SX','SY','SZ','TH','TJ','TS','TX','TY','UB','UD','UJ','UK','UP','UQ','UU','VZ','YV','VD','VE','VF','VG','QO','VM','VN','YU','RA','YX','VP','VR','VS','XU','WB','WT','WW','WZ','XA','XG','XM','XN','QA','QB','QC','QE','QF','QG','QH','QI','QJ','QK','QL','QM','QN','QQ','QR','QS','QT','QU','QV','QW','QX','QY','RB','RC','RD','RF','RG','RH','RI','RJ','RN','RO','RQ','RR','RS','RT','RU','RV','RW','RX','RY','RZ','SB','SD','SF','SG','SH','SJ','SK','SM','SN','SO','SP','SQ','SR','SS','ST','SU','SV','TA','TC','TD','TE','TF','TG','TI','TK','TL','TM','TN','TO','TP','TQ','TR','TT','TV','TZ','UA','UC','UF','UG','UH','UI','UL','UN','UO','UR','US','UT','UV','VW','VX','VY','VA','VB','VC','VH','VJ','VK','VL','VO','VQ','VT','VU','VV','XR','XS','XT','WA','WC','WD','WE','WG','WH','WI','WJ','WK','WL','WM','WN','WO','WP','WQ','WR','WU','WV','WX','WY','XB','XC','XD','XE','XF','XH','XI','XJ','XK','XL','XP','37','44','HQ','EO','YP','JD','FL','FK','IZ','YM','FN','HR','YQ','LL','YJ','HV','FO','HW'

[SQL77]

用参数替换!!!太长了晕

[Yang_]

当然有优化余地，因为你的SQL太长，说说思路你自己写


你连接了MainDataV D, CategoriesT A, CategoriesT B, SubGeoT S, PLineT Y 五个表，我不知道你哪个表数据多，但是MainDataV D应该是多的，而且条件都在这个表，所以可以先
select ... --选择需要的字段
into #t
from MainDataV
where ... -- 把这个表的条件都复制过来

如果其他表不大，则可以连接#t和其他表直接得出结果，如果有大的，则考虑继续分步

[让你望见影子的墙]

头晕。应该有。觉得先把逻辑关系理清楚

[Morgan_ma]

,'YB','JB','JJ','YS','YC','GM','HY','MV','LC','LK','HP','YD','LG','HC','YE','HN','JG','LI','JK','HX','LE','YF','85','YG','YH','HL','17','IA','LJ','FH','YR','LF','72','YI','LM','JI','JH','HZ','XO','KX','IJ','KI','KM','KN','KO','KP','YL','AY','KQ','67','KR','KS','GW','KT','GX','KU','YN','KW','YK','D-AM IPG-GLOBAL','KH','AW','YY','PY','LB','D-EU IPG-GLOBAL'))
AND (DSource IN('C','D','I') OR DSource IN ('C','D','I') OR DSource IN ('X', 'X')) AND DType = 'A' GROUP BY S.[Description], Y.[Description], Y.LevelCode, B.[Description], B.HeadQuarter)
AS TempQuery GROUP BY SubGeo, PLine, LevelCode, Account, HQ ORDER BY SubGeo, PLine, LevelCode, Account, HQ

[-晴天]

语句的最后差右括号.

[Morgan_ma]

谢谢楼上几位的回复，思路是这样的

主要就是这几张表MainDataV D, CategoriesT A, CategoriesT B, SubGeoT S, PLineT Y ，其中MainDataV是主表有几百万条数据。

这个sql是其中三个小的sql结果union all在一起，然后取整体的group by求sum。现在这句语句大概要运行30分钟到45分钟左右。

怎么样才能更快些？

[Morgan_ma]

楼上几位可以把每层楼的sql整合在一起，就是完整的了。

[-晴天]

啊!
后面还有那么多啊.
能用 = 的,就不用 IN.

[Morgan_ma]

是不是主要问题在IN里的参数太多？有没有更好的替换方法？

[Morgan_ma]

楼上的意思是IN整体用=和or来替换？

[ai_li7758521]

有的。你的WHERE后面有很多条件是之关系一个表的。如果这些条件筛掉比较多的记录的话，像yang说的可以把结果先存到临时表。

[-晴天]

DSource IN('I') OR DSource IN ('I') OR DSource IN ('I', 'I')
->
DSource ='I' OR DSource ='I' OR DSource ='I'

类似的句法似乎还有.

[Morgan_ma]

先筛选中maindatav中符合条件的记录？

[-晴天]

[Quote=引用 13 楼 Morgan_ma 的回复:]
楼上的意思是IN整体用=和or来替换？
[/Quote]
不是,象上面的那种就不要用IN.

[ai_li7758521]

参看：用临时表改善嵌套SQL语句的执行速度
http://www.cnblogs.com/mywebname/articles/946342.html

[Jamy325]

在数据库中做几个视图，然后在视图中查询。代码就不会那么长了吧~

[-晴天]

临时表也是一种办法,不过,像楼主这样的查询,常量都用IN,到是觉得先改其中一句为 or 试试.另外,恐怕索引对查询速度的影响更大,那些用来IN的列,应该都建索引.

[-晴天]

比较,是在寄存器中做的,速度最快.
带IN的比较,要在存储器中建立循环,有循环指针的赋值操作,速度可能要慢不少.