SELECT DISTINCT
dbo.ExitInfor.ID, dbo.ExitInfor.Code, dbo.ExitInfor.TruckNum, dbo.ExitInfor.Num,
dbo.ExitInfor.TheDate, dbo.ExitInfor.Operator, dbo.ExitInfor.BillMan,
dbo.ExitInfor.Re, dbo.EnterInfor.Name, dbo.EnterInfor.XinHao,
dbo.EnterInfor.GuiGe, dbo.EnterInfor.Product, dbo.EnterInfor.Unit,
dbo.ExitInfor.PerFee, dbo.ExitInfor.AllFee, n.AvgFee, dbo.ExitInfor.Audit,
dbo.ExitInfor.Auditer, dbo.ExitInfor.AuditDate, dbo.EnterInfor.TheID,
dbo.EnterInfor.KuWei
FROM dbo.EnterInfor INNER JOIN
dbo.ExitInfor ON dbo.EnterInfor.Code = dbo.ExitInfor.Code INNER JOIN
(SELECT Code, CONVERT(decimal(10, 2), SUM(Num * PerFee) / (CASE SUM(Num)
WHEN 0 THEN 1 ELSE SUM(Num) END)) AS AvgFee
FROM dbo.EnterInfor AS EnterInfor_1
GROUP BY Code) AS n ON n.Code = dbo.EnterInfor.Code
注:
1)ExitInfor为出库表,EnterInfor为入库表
2)这个语句用来生成一个出库的视图
3)EnterInfor_1,是用来计算加权平均的入库价格
4)字段Code,不是主键问题:
目前,这个视图中的数量为11630,ExitInfor表中的数量为9937,两个数量不等,理论上应该是等的啊!
请帮忙看看应该怎么修改这个SQL语句??关注……………………
可以把enterInfor和exitInfor进行加总
select t1.Code,t1.入库数量,t2.出库数量,n.AvgFee
from (select code,sum(入库数量) as 入库数量
from EnterInfor
group by code) t1
left join (elect code,sum(Num) as 出库数量
from ExitInfor
group by code) t2 on t1.code = t2.code
left join (SELECT Code, CONVERT(decimal(10, 2), SUM(Num * PerFee) / (CASE SUM(Num)
WHEN 0 THEN 1 ELSE SUM(Num) END)) AS AvgFee
FROM dbo.EnterInfor
GROUP BY Code) AS n on n.code = t1.code
最终目的就是保证连接是一对一的连接,不能存在有一对多的连接,否则这些连接的记录会多出来