有关多关键字结构表的查询优化方案.

Question

select distinct a.MatCode,a.Spec,a.ColorName,
InAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'),
OutAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'),
From vMatIn a 
Group By  a.MatCode,a.Spec,a.ColorName
Order by  a.MatCode,a.Spec,a.ColorName这一条SQL本身就有问题
distinct 本身就有分组的效果。而且再加上Order by 还能快起来才怪InAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'),
OutAmt=(select sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'),
而且要实现你这个功能，你这种写法是最差的一种。

Answer 1

建议如下使用declare @piInAmt int,@piOutAmt int
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'select @piInAmt=sum(b.Amt)  From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='出库'
select distinct a.MatCode,a.Spec,a.ColorName,
@piInAmt,
@piOutAmt,
From vMatIn a 
Group By  a.MatCode,a.Spec,a.ColorName
Order by  a.MatCode,a.Spec,a.ColorName这样会很快的，１０几万记录是很小的，几千万的记录，都不会慢

Answer 2

1.
select distinct a.MatCode,a.Spec,a.ColorName,
InAmt=
case b.Reason
 when '入库' then sum(b.Amt)
end,OutAmt=
case b.Reason
 when '出库' then sum(b.Amt)
end,From vMatIn a ,
vMatInout b
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname 
Group By  a.MatCode,a.Spec,a.ColorName
Order by  a.MatCode,a.Spec,a.ColorName2\使用临时表,可能快些select MatCode,Spec,ColorName,sum(Amt) as InAmt into #temp From vMatInout  where Reason='入库'
group by MatCode,Spec,ColorNameselect MatCode,Spec,ColorName,sum(Amt) as OutAmt into #temp1 From vMatInout  where Reason='出库'
group by MatCode,Spec,ColorNameselect distinct a.MatCode,a.Spec,a.ColorName,
b.InAmt=
c.OutAmt
From vMatIn a ,#temp b,#temp1 cwhere a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname 
a.MatCode=c.MatCode and a.Spec=c.Spec and a.ColorName=c.Colorname 
Group By  a.MatCode,a.Spec,a.ColorName
Order by  a.MatCode,a.Spec,a.ColorName你自己测试一下.

Answer 3

方法一:服务器: 消息 8120，级别 16，状态 1，行 1
列 'b.reason' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

Answer 4

declare @piInAmt int,@piOutAmt int
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'根本不支持这种写法.:
列前缀 'a' 与查询中所用的表名或别名不匹配。

Answer 5

select distinct a.MatCode,a.Spec,a.ColorName,
c.InAmt,d.OutAmt,From vMatIn a ,
vMatInout b，
（
select MatCode,Spec,ColorName,sum(Amt) as InAmt into #temp From vMatInout  where Reason='入库'
group by MatCode,Spec,ColorName）　as c,（
select MatCode,Spec,ColorName,sum(Amt) as OutAmt into #temp From vMatInout  where Reason='出库'
group by MatCode,Spec,ColorName）　as dwhere a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and 
a.MatCode=c.MatCode and a.Spec=c.Spec and a.ColorName=c.Colorname  and
a.MatCode=d.MatCode and a.Spec=d.Spec and a.ColorName=d.Colorname 
Group By  a.MatCode,a.Spec,a.ColorName
Order by  a.MatCode,a.Spec,a.ColorName

Answer 6

declare @piInAmt int,@piOutAmt int
select @piInAmt=sum(b.Amt) From vMatInout b where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname and b.Reason='入库'根本不支持这种写法.:
列前缀 'a' 与查询中所用的表名或别名不匹配。
-----------------------------
我写错了
，这样测试一下：select distinct a.MatCode,a.Spec,a.ColorName,b.Reason,
InAmt=
case b.Reason
 when '入库' then sum(b.Amt)
end,OutAmt=
case b.Reason
 when '出库' then sum(b.Amt)
end,From vMatIn a ,
vMatInout b
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname 
and (b.Reason="出库＂or b.Reason="入库")
Group By  a.MatCode,a.Spec,a.ColorName，b.Reason
Order by  a.MatCode,a.Spec,a.ColorName这样肯定可以了，只是多了一列b.Reason

Answer 7

select 里面的b.Reason去掉看看行不行，但group by 里面肯定要加b.Reason的．

Answer 8

你那个distinct要本没有取到作用.它的结果是
a.MatCode,a.Spec,a.ColorName,b.Reason,InAmt,OutAmt为唯一的.有两条记录了.入库一条,出库一条.

Answer 9

就是因为group by 里加了reason,所以导致出来就是两条了.

Answer 10

select distinct a.MatCode,a.Spec,a.ColorName,InAmt=
case b.Reason
 when '入库' then sum(b.Amt)
end,OutAmt=
case b.Reason
 when '出库' then sum(b.Amt)
end,From vMatIn a ,
vMatInout b
where a.MatCode=b.MatCode and a.Spec=b.Spec and a.ColorName=b.Colorname 
and (b.Reason="出库＂or b.Reason="入库")
Group By  a.MatCode,a.Spec,a.ColorName，b.Reason
Order by  a.MatCode,a.Spec,a.ColorName把select 中的b.Reason去掉应该也可以的，只在group by 中加b.Reason，这个方法应该最快的-----------------------------
我的哪个写法芽 ？
这个？？
要修改，应该比你的快
select distinct a.MatCode,a.Spec,a.ColorName,
c.InAmt,d.OutAmt,From vMatIn a ,
（
select MatCode,Spec,ColorName,sum(Amt) as InAmt From vMatInout  where Reason='入库'
group by MatCode,Spec,ColorName）　as c,（
select MatCode,Spec,ColorName,sum(Amt) as OutAmt From vMatInout  where Reason='出库'
group by MatCode,Spec,ColorName）　as dwhere 
a.MatCode=c.MatCode and a.Spec=c.Spec and a.ColorName=c.Colorname  and
a.MatCode=d.MatCode and a.Spec=d.Spec and a.ColorName=d.Colorname 
Order by  a.MatCode,a.Spec,a.ColorName
------------------------------
不要子集b,而且要搞错了，不用写入临时表的，把 into #temp去掉，也不用Group by 就行了，如果还不快的话，建议你表vMatIn  ,vMatInoutb
都加上联合索引MatCode+Spec+ColorName，十几万条记录，100ms以内便可以出来查询结果

Answer 11

你上面的写法实际上是先求了同条件下唯一合计,然后再连接.用临时表也可以做的.表已经建立了聚合索引了.因为matCode有时候过长.所以效率并不高.后来改成了用入库日期做索引.因为查询大部分是按日期先过滤,再按物料类别,物料编号等条件..