union all 后group by 效率

[smilyvm]

库存是动态计算的，用多个表union all 后再group by 计算合计值
类似这样

select fid,fname,sum(fnum) from (
select fid,fname,fnum from tab0
union all
select fid,fname,fnum from tab1
union all
select fid,fname,fnum from tab2
union all
select fid,fname,fnum from tab3
union all
select fid,fname,fnum from tab4
union all
select fid,fname,fnum from tab5
union all
select fid,fname,fnum from tab6
) t group by fid,fname

这样的方式union all 后 group by 之前的排序开销非常大，效率很低，不知作何优化

[smilyvm]

具体的语句如下

select 'AB' as shopname,201603 as periodid,* from (
SELECT t.ckname,upper(t.cwname) as cwname,t.fcode,upper(v.matername) as fname,v.spec as fspec,
v.color as fcolor,v.ptcolor,v.season,v.ftype ,v.fdescription0,t.funit,sum(fnum) as fnum from(
----合并各结果集----
SELECT ckname,cwname,fcode,funit,fpsjnum as fnum from of_kwperiodstock 
WHERE periodid='201603' 
UNION all 
SELECT ckname,cwname,fcode,funit,fnum from VRkdList 
WHERE ischecked2=1 and rkdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT ckname,cwname,fcode,funit,fnum from dbo.OF_WWRkMain a ,dbo.OF_WwRkDetail b 
WHERE a.WwRkid=b.Wwrkid AND ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'  
UNION all 
SELECT ckname,kwname,fcode,funit,fyknum from of_kcpd 
WHERE ischecked2=1 and periodid='201603' 
UNION all 
SELECT ckname,kwname,rfcode,rfunit,ftznum from vkctzlist 
WHERE ischecked2=1 and tzdate between  '2016-03-01' AND '2016-03-31'
UNION all 
SELECT ckname,tocw,fcode,funit,fnum from vcndblist 
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT tock,tocw,fcode,funit,fnum  from vkcdbrklist  
WHERE ischecked2=1 and billdate  between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT drck,drkw,fcode,funit,fnum from vkcdbcklist  
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT ckname,cwname,fcode,funit,-sfnum from vckdlist  
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31' 
UNION all 
SELECT ckname,cwname,fcode,funit,-fnum from vlldlist 
WHERE ischecked2=1 and lldate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT ckname,cwname,fcode,funit,-fnum from vsbcklist  
WHERE ischecked2=1 and ckdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT ckname,kwname,cfcode,cfunit,-ftznum from vkctzlist  
WHERE ischecked2=1 and tzdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT ckname,cwname,fcode,funit,-fnum from vwwcklist
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT tock,fromcw,fcode,funit,-fnum  from vkcdbrklist 
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31' 
UNION all 
SELECT ckname,fromcw,fcode,funit,-fnum  from vcndblist 
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'
UNION all 
SELECT dcck,fromcw,fcode,funit,-fnum  from vkcdbcklist 
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'

) t,vmaterial v 
WHERE t.fcode=v.matercode AND v.ShopName='AB'
AND not exists (select kwname from BD_CWINFO 
WHERE isnull(isunkskw,0)=1 and kwname=t.cwname) 
GROUP by t.ckname,t.cwname,t.fcode,t.funit,v.matername,
v.spec,v.color,v.ptcolor,v.season,v.ftype,v.fdescription0
) tt  where tt.fnum<>0

[中国风]

--按以下方式改，应该提高不少 在每个表建以下索引 CREATE INDEX IX_Test2 ON test2(fid,fname) INCLUDE(fnum) --语句，一个个表合计再+全联 SELECT name=COALESCE(a.Name,b.Name),ISNULL(a.ID,0)+ISNULL(b.ID,0) FROM (SELECT fid,fname,SUM(ID) AS ID FROM Test1 GROUP BY fid,fname) AS a FULL JOIN (SELECT fid,fname,SUM(ID) AS ID FROM Test2 GROUP BY fid,fname) AS b ON a.fname=b.fname AND a.fid=b.fid

[smilyvm]

表的索引都是建了的，现在查询结果集3万多数据需要20多秒

[smilyvm]

在视图后面加上with(nolock)有用吗

[中国风]

引用 4 楼 smilyvm 的回复:

在视图后面加上with(nolock)有用吗

可以这样用

[中国风]

脏读只是用于表操作频繁时不阻塞，对其它性能没作用 你按以上#2方式改 用full join ,性能会提高

[smilyvm]

你写的那个看不大明白

[smilyvm]

引用 6 楼 roy_88 的回复:

脏读只是用于表操作频繁时不阻塞，对其它性能没作用 你按以上#2方式改 用full join ,性能会提高

SELECT 
CkName=COALESCE(qckc.ckname,cgrk.ckname,wwrk.CkName,kcpd.CkName,kctz.CkName,cndr.CkName),
cwname=COALESCE(qckc.cwname,cgrk.cwname,wwrk.CwName,kcpd.KwName,kctz.KwName,cndr.Tocw),
fcode=COALESCE(qckc.fcode,cgrk.fcode,wwrk.FCode,kcpd.FCode,kctz.RFCode,cndr.FCode),
funit=COALESCE(qckc.funit,cgrk.funit,wwrk.FUnit,kcpd.FUnit,kctz.RFUnit,cndr.FUnit),
ISNULL(qckc.fnum,0)+ISNULL(cgrk.fnum,0)+ISNULL(wwrk.fnum,0)
+ISNULL(kcpd.fnum,0)+ISNULL(kctz.fnum,0)+ISNULL(cndr.fnum,0)
FROM (
SELECT ckname,cwname,fcode,funit,SUM(fpsjnum) as fnum from of_kwperiodstock 
WHERE periodid='201603' 
GROUP BY ckname,cwname,fcode,funit) AS qckc
FULL JOIN(
SELECT ckname,cwname,fcode,funit,SUM(fnum) AS fnum from VRkdList 
WHERE ischecked2=1 and rkdate between '2016-03-01' AND '2016-03-31'
GROUP BY ckname,cwname,fcode,funit) AS cgrk
ON qckc.CkName=cgrk.CkName AND cgrk.CwName = qckc.CwName 
AND cgrk.FCode = qckc.FCode AND cgrk.FUnit = qckc.FUnit
FULL JOIN(
SELECT ckname,cwname,fcode,funit,SUM(fnum) AS fnum from dbo.OF_WWRkMain a ,dbo.OF_WwRkDetail b 
WHERE a.WwRkid=b.Wwrkid AND ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'  
GROUP BY ckname,cwname,fcode,funit) as wwrk
ON wwrk.CkName = cgrk.CkName AND wwrk.CwName = cgrk.CwName
AND wwrk.FCode=cgrk.FCode AND wwrk.FUnit=cgrk.FUnit
FULL JOIN(
SELECT ckname,kwname,fcode,funit,SUM(fyknum) AS fnum from of_kcpd 
WHERE ischecked2=1 and periodid='201603' 
GROUP BY ckname,kwname,fcode,funit) AS kcpd
ON kcpd.CkName = wwrk.CkName AND kcpd.KwName=wwrk.CwName
AND kcpd.FCode = wwrk.FCode AND kcpd.FUnit = wwrk.FUnit
FULL JOIN(
SELECT ckname,kwname,rfcode,rfunit,SUM(ftznum) AS fnum from vkctzlist 
WHERE ischecked2=1 and tzdate between  '2016-03-01' AND '2016-03-31'
GROUP BY ckname,kwname,rfcode,rfunit) AS kctz
ON kctz.CkName = kcpd.CkName AND kctz.KwName = kcpd.KwName
AND kctz.RFCode=kcpd.FCode AND kctz.RFUnit=kcpd.FUnit
FULL JOIN(
SELECT ckname,tocw,fcode,funit,SUM(fnum) AS fnum from vcndblist 
WHERE ischecked2=1 and billdate between '2016-03-01' AND '2016-03-31'
GROUP BY ckname,tocw,fcode,funit) AS cndr
ON cndr.CkName = kctz.CkName AND cndr.tocw=kctz.KwName
AND cndr.FCode=kctz.RFCode AND cndr.FUnit=kctz.RFCode

是这样写吗？？

[smilyvm]

貌似上面的写法不对啊，有很多Null值

[中国风]

引用 9 楼 smilyvm 的回复:

貌似上面的写法不对啊，有很多Null值

如果没有值为NULL是原因你漏了表对象或有表数据存在NULL
funit=COALESCE(
fcode=COALESCE(

[smilyvm]

引用 10 楼 roy_88 的回复:

[quote=引用 9 楼 smilyvm 的回复:] 貌似上面的写法不对啊，有很多Null值

如果没有值为NULL是原因你漏了表对象或有表数据存在NULL funit=COALESCE( fcode=COALESCE([/quote] COALESCE后要加所有的表名吧

[smilyvm]

多表full join 连接的条件要怎么加 SELECT name=COALESCE(a.Name,b.Name,c.Name),ISNULL(a.ID,0)+ISNULL(b.ID,0)+ISNULL(c.ID,0) FROM (SELECT fid,fname,SUM(ID) AS ID FROM Test1 GROUP BY fid,fname) AS a FULL JOIN (SELECT fid,fname,SUM(ID) AS ID FROM Test2 GROUP BY fid,fname) AS b ON a.fname=b.fname AND a.fid=b.fid FULL JOIN (SELECT fid,fname,SUM(ID) AS ID FROM Test3 GROUP BY fid,fname) AS c ON a.fname=c.fname AND a.fid=c.fid 这样对吗

[中国风]

对，就是这样用

[smilyvm]

我发现数量并没有相加

[smilyvm]

B表和C表没有关联，导致可能会有多条记录