一个查询语句计算的错误,请大锅们帮忙瞧一瞧败哪里有错?

壹号网络官方账号

2003-12-22 11:35:16

--这是存储过程
IF object_id('tempdb..#Water') IS NULL
CREATE TABLE #Water
(
DeptProcName varchar(50),
EquName varchar(50),
OneDIWater numeric(10,2),
OneWater numeric(10,2),
TaiNumber int,
MidWater numeric(10,2),
AllWater numeric(10,2)
)
ELSE delete #Water --清空表

INSERT INTO #Water
SELECT Pub_DeptProcInfo.DeptProcName, Equ_EquInfo.EquName,
STR(AVG(Equ_EquInfo.DIWater), 10, 2) as OneDIWater,
STR(AVG(Equ_EquInfo.Water), 10, 2) as OneWater,
COUNT(EquName) as TaiNumber,
str(AVG(Equ_EquInfo.DIWater/0.07+Equ_EquInfo.Water),10,2) as MidWater,
AllWater=0.0
FROM Equ_EquInfo,Pub_DeptProcInfo
where Equ_EquInfo.DeptProcNo=Pub_DeptProcInfo.DeptProcNo
GROUP BY Pub_DeptProcInfo.DeptProcName, Equ_EquInfo.EquName

--以上数据都正确无误地
Update #Water
Set AllWater=MidWater*TaiNumber

--这也可以

--以下的就有问题,既是取消上面的更新的也会的.

Select DeptProcName,EquName,OneDiWater,OneWater,TaiNumber,AllWater,
(select sum(AllWater) from #Water where #Water.DeptProcName=p.DeptProcName) As Litter
from #Water p

--结果如下:
DeptProcName,EquName, OneDiWater, OneWater,TaiNumber,AllWater,Litter
黑化棕化加药/储药缸 .00 .00 1 .00 74.85
黑化棕化生产线 2.50 4.07 1 39.78 74.85
钻孔(S) 钻靶机 .00 .00 7 .00 .00
钻孔(M) 钻咀测经仪 .00 .00 2 .00 .00
钻孔(M) 钻咀装坏机 .00 .00 1 .00 .00
铣板钻嘴测径仪 .00 .00 1 .00 1.20

--本来结果应该是黑化的工序小计为39.78+0.00
DeptProcName,EquName, OneDiWater, OneWater,TaiNumber,AllWater,Litter
黑化棕化加药/储药缸 .00 .00 1 .00 39.78
黑化棕化生产线 2.50 4.07 1 39.78 39.78
钻孔(S) 钻靶机 .00 .00 7 .00 .00
钻孔(M) 钻咀测经仪 .00 .00 2 .00 .00
钻孔(M) 钻咀装坏机 .00 .00 1 .00 .00
铣板钻嘴测径仪 .00 .00 1 .00 .00

--另外的同样功能的存储过程

IF object_id('tempdb..#tt') IS NULL
CREATE TABLE #tt(DeptProcName varchar(50), EquType varchar(50), EquName varchar(50), EquDes varchar(50), TaiNumber int, OneTaiPower numeric(10,2), AllPower numeric(10,2))
ELSE delete #tt

INSERT
INTO #tt
SELECT a.DeptProcName, Equ_EquInfo.EquType, Equ_EquInfo.EquName,
Equ_EquInfo.EquDes, COUNT(Equ_EquInfo.EquName) AS TaiNumber,
STR(AVG(Equ_EquInfo.Power), 10, 2) AS OneTaiPower,
SUM(Equ_EquInfo.Power) AS AllPower
FROM Equ_EquInfo INNER JOIN
Pub_DeptProcInfo a ON Equ_EquInfo.DeptProcNo = a.DeptProcNo
GROUP BY a.DeptProcName, Equ_EquInfo.EquName, Equ_EquInfo.EquDes, Equ_EquInfo.EquType

--这就是相同的语句

Select *,(select sum(Allpower) from #tt where #tt.DeptProcName=a.DeptProcName) As Litter
from #tt a
GO

--结果正确:
FQC(S) 矫平机 / 1 .20 .20 14.40
FQC(S) 矫平机 HL-JPJ100 1 .30 .30 14.40
FQC(S) 吸缩包装机 FM-76 1 1.90 1.90 14.40
FQC(S) 吸缩机 FM-5540 1 2.00 2.00 14.40
FQC(S) 真空包装机 ZBZ-588 1 10.00 10.00 14.40

...全文