求大佬伸出猿手，2个表单独查询都只需要1秒，leftjoin到一起就要差不多100秒

terry367

表1和表2都只有200多行的数据求大佬伸出猿手

1. 
   
2. select d.款号,d.大类,d.数量,c.店数 from
   
3. /******表1******/
   
4. (SELECT SHANGPIN.spdm as 款号,[FJSX2].sxmc as 大类
   
5. ,COALESCE(sum([SG_Gatherings].[fQuantity]),0) as 数量
   
6. FROM [BS3000+_000_2016].[dbo].[SG_Gatherings]
   
7. left join[BS3000+_000_2016].[dbo].[SG_Gathering] on [SG_Gatherings].[vMBillID]=[SG_Gathering].[vMBillID]
   
8. left join[BS3000+_000_2016].[dbo].KEHU on khdm=[SG_Gatherings].vshop
   
9. left join[BS3000+_000_2016].[dbo].[ShangPin] on vStyle=spdm
   
10. left join[BS3000+_000_2016].[dbo].[FJSX2] on fjsx2=[FJSX2].sxdm
    
11. WHERE bcancel='0' and bputup='0' and FJSX4='g'and fjsx3='2' and fjsx2.sxmc!='配件' AND fjsx2.sxmc!='礼品' and fjsx10!='950'
    
12. and CONVERT(date,dtdate)>=CONVERT(date,getdate()-7) and CONVERT(date,dtdate)<CONVERT(date,getdate())
    
13. group by SHANGPIN.spdm,[FJSX2].sxmc
    
14. )as d   
    
15. left join
    
16. /******表2******/
    
17. (
    
18. select b.款 as 款,count(店) as 店数 from(
    
19. select ckmc as 店,款 as 款,sum(数量)as 库存 from(
    
20. SELECT ckmc,shangpin.spdm as 款
    
21. ,COALESCE(sum(SPKCB.[SL]),0) as 数量,数据来源=N'库存'
    
22. FROM [BS3000+_000_2016].[dbo].SPKCB
    
23. left join[BS3000+_000_2016].[dbo].[ShangPin]on SPKCB.spdm=[ShangPin].spdm
    
24. left join[BS3000+_000_2016].[dbo].cangku on cangku.ckdm=SPKCB.ckdm
    
25. where cangku.byzd5='1' and FJSX4='g'and fjsx3='2' and fjsx2!='0' and SL!=0
    
26. group by ckmc,shangpin.spdm
    
27. union all
    
28. 
    
29. /****调出未收****/
    
30. SELECT ckmc,        shangpin.spdm
    
31. ,COALESCE(sum(spycdmx.[SL]),0) as 数量,数据来源=N'调出未收'
    
32. FROM [BS3000+_000_2016].[dbo].spycdmx
    
33. left join[BS3000+_000_2016].[dbo].[ShangPin]on spycdmx.spdm=[ShangPin].spdm
    
34. left join[BS3000+_000_2016].[dbo].spycd on spycd.djbh=spycdmx.djbh
    
35. left join[BS3000+_000_2016].[dbo].CANGKU on spycd.dm2=cangku.ckdm
    
36. 
    
37. where cangku.byzd5='1' and FJSX4='g'and fjsx3='2' and fjsx2!='0' and spycd.sh=0
    
38. group by ckmc, shangpin.spdm
    
39. 
    
40. union all
    
41. 
    
42. /****已发未收****/
    
43. SELECT ckmc ,shangpin.spdm
    
44. ,COALESCE(sum(sdphdmx.[SL]),0) as 数量,数据来源=N'已发未收'
    
45. FROM [BS3000+_000_2016].[dbo].sdphdmx
    
46. left join[BS3000+_000_2016].[dbo].[ShangPin] on sdphdmx.spdm=[ShangPin].spdm
    
47. left join[BS3000+_000_2016].[dbo].sdphd on sdphd.djbh=sdphdmx.djbh
    
48. left join[BS3000+_000_2016].[dbo].CANGKU on ckdm=sdphd.DM1
    
49. where cangku.byzd5='1' and FJSX4='g'and fjsx3='2' and fjsx2!='0'
    
50. and sdphd.ys=1 and sdphd.sh=0
    
51. group by ckmc, shangpin.spdm)as a
    
52. group by ckmc,款)as b
    
53. where 库存 >2
    
54. group by b.款
    
55. )as c on d.款号=c.款
    

复制代码

terry367

大佬们，开工啦，帮帮

鑫尼达

现在发现，真的很佩服那些经常帮人解答问题的人。这些表名字段名别名眼睛都要看花了