select invent.cDepotId,invent.cprodid
,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity
,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCentiare
,case when in_iSuAmt is null then 0 else in_iSuAmt end in_iSuAmt
,case when ou_iQuanTity is null then 0 else ou_iQuanTity end ou_iQuanTity
,case when ou_iSuCentiare is null then 0 else ou_iSuCentiare end ou_iSuCentiare
from (
select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit
from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId
)invent left join (
select cDepotId,paperlog.cProdId,sum(iQuanTity)in_iQuanTity,sum(iSuCentiare)in_iSuCentiare,sum(iSuAmt)in_iSuAmt
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='入库'
group by cDepotId,cProdId) inPaperlog on invent.cProdId=inPaperlog.cProdid and invent.cDepotId=inPaperlog.cDepotId
left join (
select cDepotId,paperlog.cProdId,sum(iQuanTity)ou_iQuanTity,sum(iSuCentiare)ou_iSuCentiare
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='出库'
group by cDepotId,cProdId
)ouPaperlog on invent.cProdId=ouPaperlog.cProdId and invent.cDepotId=ouPaperlog.cDepotId
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0
order by invent.cprodid,invent.cDepotId这个语句,执行不了,Sql服务器好像死循环了
如果把
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0
这句给删除了,查询出来的记录也才80000多条
为什么会这样呢?求师兄师姐给指点一下,谢谢!
,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity
,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCentiare
,case when in_iSuAmt is null then 0 else in_iSuAmt end in_iSuAmt
,case when ou_iQuanTity is null then 0 else ou_iQuanTity end ou_iQuanTity
,case when ou_iSuCentiare is null then 0 else ou_iSuCentiare end ou_iSuCentiare
from (
select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit
from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId
)invent left join (
select cDepotId,paperlog.cProdId,sum(iQuanTity)in_iQuanTity,sum(iSuCentiare)in_iSuCentiare,sum(iSuAmt)in_iSuAmt
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='入库'
group by cDepotId,cProdId) inPaperlog on invent.cProdId=inPaperlog.cProdid and invent.cDepotId=inPaperlog.cDepotId
left join (
select cDepotId,paperlog.cProdId,sum(iQuanTity)ou_iQuanTity,sum(iSuCentiare)ou_iSuCentiare
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='出库'
group by cDepotId,cProdId
)ouPaperlog on invent.cProdId=ouPaperlog.cProdId and invent.cDepotId=ouPaperlog.cDepotId
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0
order by invent.cprodid,invent.cDepotId这个语句,执行不了,Sql服务器好像死循环了
如果把
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0
这句给删除了,查询出来的记录也才80000多条
为什么会这样呢?求师兄师姐给指点一下,谢谢!
select invent.cDepotId,invent.cprodid
,case when in_iQuantity is null then 0 else in_iQuantity end in_iQuantity
,case when in_iSuCentiare is null then 0 else in_iSuCentiare end in_iSuCentiare
,case when in_iSuAmt is null then 0 else in_iSuAmt end in_iSuAmt
,case when ou_iQuanTity is null then 0 else ou_iQuanTity end ou_iQuanTity
,case when ou_iSuCentiare is null then 0 else ou_iSuCentiare end ou_iSuCentiare
from (
select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit
from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId
)invent
left join (
select cDepotId,paperlog.cProdId,
sum(case when papClass.cPapcName = '入库' then iQuanTity else null end) as in_iQuanTity,
sum(case when papClass.cPapcName = '入库' then iSuCentiare else null end) as iSuCentiare,
sum(case when papClass.cPapcName = '入库' then iSuAmt else null end) as iSuAmt,
sum(case when papClass.cPapcName = '出库' then iQuanTity else null end) as ou_iQuanTity,
sum(case when papClass.cPapcName = '出库' then iSuCentiare else null end) as ou_iSuCentiare
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7
group by cDepotId,cProdId
) Paperlog
on invent.cProdId=Paperlog.cProdid and invent.cDepotId=Paperlog.cDepotId
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0
order by invent.cprodid,invent.cDepotId
--try2
select invent.cDepotId,invent.cprodid
,isnull(in_iQuantity,0) as in_iQuantity
,isnull(in_iSuCentiare,0) as in_iSuCentiare
,isnull(in_iSuAmt,0) as in_iSuAmt
,isnull(ou_iQuanTity,0) as ou_iQuanTity
,isnull(ou_iSuCentiare,0) as ou_iSuCentiare
from (
/*这个子查询能再优化下吗?*/
select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit
from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId
)invent
left join (
select cDepotId,paperlog.cProdId,
sum(case when papClass.cPapcName = '入库' then iQuanTity else null end) as in_iQuanTity,
sum(case when papClass.cPapcName = '入库' then iSuCentiare else null end) as iSuCentiare,
sum(case when papClass.cPapcName = '入库' then iSuAmt else null end) as iSuAmt,
sum(case when papClass.cPapcName = '出库' then iQuanTity else null end) as ou_iQuanTity,
sum(case when papClass.cPapcName = '出库' then iSuCentiare else null end) as ou_iSuCentiare
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7
group by cDepotId,cProdId
) Paperlog
on invent.cProdId=Paperlog.cProdid and invent.cDepotId=Paperlog.cDepotId
where in_iQuantity<>0 or in_iSuCentiare<>0 or in_iSuAmt<>0 or ou_iQuanTity<>0 or ou_iSuCentiare<>0
order by invent.cprodid,invent.cDepotId
第一种方式
1、使用存储过程或sql自定义函数,
2、分拆以上的sql语句,单独三个临时表,并给临时表创建索引
临时表一
select depot.cDepotId,cDepotName,invent.cClassId,cProdid,cProdName,iCentiare,cSpecif,cUnit
from invent FULL OUTER JOIN depot on invent.cProdid<>depot.cDepotId临时表二
select cDepotId,paperlog.cProdId,sum(iQuanTity)in_iQuanTity,sum(iSuCentiare)in_iSuCentiare,sum(iSuAmt)in_iSuAmt
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='入库'
group by cDepotId,cProdId) inPaperlog on invent.cProdId=inPaperlog.cProdid and invent.cDepotId=inPaperlog.cDepotId 临时表三
select cDepotId,paperlog.cProdId,sum(iQuanTity)ou_iQuanTity,sum(iSuCentiare)ou_iSuCentiare
from paperlog,papClass
where paperlog.cPapcType=papClass.cPapcType and paperlog.iyear=2012 and paperlog.imonth=7 and papClass.cPapcName='出库'
group by cDepotId,cProdId然后再进行表关联
在联表查询时候,进行group by效率上肯定是很低的,还不如单独外面先进行group by 再进行联表查询操作另外这里也可以使用CTE 操作,这样就避免使用临时表