把上述語句改一下:將ID_Machine改成UniCode : select Workday ,B.UniCode , Sum(Count_QC) as 檢驗數 , Sum( Count_NG) as 不良數 ,AVG(Proportion_NG) as 不良率1 ,cast(Sum( Count_NG) as float) / cast(Sum(Count_QC) as float ) as 不良率2 from QC_IPQCRec A left Join Machine B On (A.ID_Machine = B.ID ) where Workday >= '2004-08-01' group by Workday ,B.UniCode order by Workday , B.UniCode返回結果: Workday ID_Machine 檢驗數 不良數 不良率1 不良率2 ----------------------------------------------------------------------------- 2004-08-01 ATE#01 45 2 .02000000 4.4444444444444446E-2 2004-08-01 ATE#02 50 0 .00000000 0.0 2004-08-01 ATE#03 67 1 .01000000 1.4925373134328358E-2 2004-08-01 ATE#05 37 1 .01666666 2.7027027027027029E-2 2004-08-01 ATE#06 35 1 .03333333 2.8571428571428571E-2 2004-08-01 ATE#07 50 1 .02222222 0.02 2004-08-01 ATE#08 55 1 .01250000 1.8181818181818181E-2 2004-08-01 ATE#09 70 5 .05400000 7.1428571428571425E-2 2004-08-01 ATE#11 30 2 .04000000 6.6666666666666666E-2 2004-08-01 ATE#12 20 0 .00000000 0.0 2004-08-01 印機#01 43 6 .11750000 0.13953488372093023 2004-08-01 印機#03 44 6 .20089285 0.13636363636363635多了一個Left Join 結果大不一樣?
改成numeric 結果還是不對: update QC_IPQCRec set set Count_NG = 0 where Count_NG = NULL update QC_IPQCRec set set Count_QC = 0 where Count_QC = NULL update QC_IPQCRec set Proportion_NG = cast(Count_NG as numeric )/ cast(Count_QC as numeric)select Workday ,B.UniCode , Sum(Count_QC) as 檢驗數 , Sum( Count_NG) as 不良數 , AVG(Proportion_NG) as 不良率1, cast(Sum( Count_NG) as numeric)/cast(Sum(Count_QC) as numeric) as 不良率2 from QC_IPQCRec A left Join Machine B On (A.ID_Machine = B.ID ) where Workday >= '2004-08-01' group by Workday ,B.UniCode order by Workday , B.UniCode問題變成: AVG(cast(Count_NG as numeric )/ cast(Count_QC as numeric), cast(Sum( Count_NG) as numeric)/cast(Sum(Count_QC) as numeric)為什麼不等?()
select Workday ,B.UniCode ,
Sum(Count_QC) as 檢驗數 , Sum( Count_NG) as 不良數 ,AVG(Proportion_NG) as 不良率1
,cast(Sum( Count_NG) as float) / cast(Sum(Count_QC) as float ) as 不良率2
from QC_IPQCRec A
left Join Machine B On (A.ID_Machine = B.ID )
where Workday >= '2004-08-01'
group by Workday ,B.UniCode
order by Workday , B.UniCode返回結果:
Workday ID_Machine 檢驗數 不良數 不良率1 不良率2
-----------------------------------------------------------------------------
2004-08-01 ATE#01 45 2 .02000000 4.4444444444444446E-2
2004-08-01 ATE#02 50 0 .00000000 0.0
2004-08-01 ATE#03 67 1 .01000000 1.4925373134328358E-2
2004-08-01 ATE#05 37 1 .01666666 2.7027027027027029E-2
2004-08-01 ATE#06 35 1 .03333333 2.8571428571428571E-2
2004-08-01 ATE#07 50 1 .02222222 0.02
2004-08-01 ATE#08 55 1 .01250000 1.8181818181818181E-2
2004-08-01 ATE#09 70 5 .05400000 7.1428571428571425E-2
2004-08-01 ATE#11 30 2 .04000000 6.6666666666666666E-2
2004-08-01 ATE#12 20 0 .00000000 0.0
2004-08-01 印機#01 43 6 .11750000 0.13953488372093023
2004-08-01 印機#03 44 6 .20089285 0.13636363636363635多了一個Left Join 結果大不一樣?
改成numeric 結果還是不對:
update QC_IPQCRec set
set Count_NG = 0 where Count_NG = NULL
update QC_IPQCRec set
set Count_QC = 0 where Count_QC = NULL
update QC_IPQCRec set
Proportion_NG = cast(Count_NG as numeric )/ cast(Count_QC as numeric)select Workday ,B.UniCode ,
Sum(Count_QC) as 檢驗數 ,
Sum( Count_NG) as 不良數 ,
AVG(Proportion_NG) as 不良率1,
cast(Sum( Count_NG) as numeric)/cast(Sum(Count_QC) as numeric) as 不良率2
from QC_IPQCRec A
left Join Machine B On (A.ID_Machine = B.ID )
where Workday >= '2004-08-01'
group by Workday ,B.UniCode
order by Workday , B.UniCode問題變成:
AVG(cast(Count_NG as numeric )/ cast(Count_QC as numeric),
cast(Sum( Count_NG) as numeric)/cast(Sum(Count_QC) as numeric)為什麼不等?()
板块 专家分 信誉分 等级
.NET技术 1107 100
Web 开发 691 100
Java 612 100
其他开发语言 73 100
扩充话题 71 100
MS-SQL Server 29 100
Delphi 25 100
其他数据库开发 20 100
VB 18 100
PowerBuilder 12 100
移动平台 12 100
Windows专区 10 100
Linux/Unix社区 5 100
Oracle 5 100
软件培训/认证/考试 3 100
硬件/嵌入开发 0 94
软件工程/管理 0 100
专题开发 0 100
多媒体/设计 0 100
社区支持 0 100
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ID_Station] [int] NOT NULL ,
[Workday] [datetime] NOT NULL ,
[QCChecker] [varchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[ID_Shift] [int] NULL ,
[ID_Machine] [int] NULL ,
[ID_MKSheet] [int] NULL ,
[QCTime] [datetime] NULL ,
[MakeDayNum] [varchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[ID_Manufacture] [int] NULL ,
[ID_ManuParts] [int] NULL ,
[WorkingProcedureNumber] [int] NULL ,
[WorkingProcedure] [int] NULL ,
[Count_QC] [int] NULL ,
[Count_NG] [int] NULL ,
[Count_Waster] [int] NULL ,
[Count_Repair] [int] NULL ,
[Proportion_NG] [numeric](18, 8) NULL ,
[QC_om] [int] NULL ,
[QC_IR] [numeric](18, 4) NULL ,
[QC_TW] [int] NULL ,
[QC_WB] [int] NULL ,
[QC_OS] [int] NULL ,
[Memo] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CreatDate] [datetime] NULL ,
[OPName] [varchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[OPDateTime] [datetime] NULL ,
[Checkman] [varchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CheckTime] [datetime] NULL ,
[CheckedResult] [int] NULL ,
[CheckHistory] [text] COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO數據太多。怎麼辦......我把以下語句的返回結果貼出來。update QC_IPQCRec
set Count_NG = 0 where Count_NG = NULL
update QC_IPQCRec
set Count_QC = 0 where Count_QC = NULL
update QC_IPQCRec
set Proportion_NG = cast(Count_NG as numeric )/ cast(Count_QC as numeric)select Workday ,ID_Machine ,
Sum(Count_QC) as 檢驗數 ,
Sum( Count_NG) as 不良數 ,
AVG(Proportion_NG) as 不良率1,
AVG(cast(Count_NG as numeric )/ cast(Count_QC as numeric)) 不良率2,
cast(Sum( Count_NG) as numeric)/cast(Sum(Count_QC) as numeric) as 不良率3
from QC_IPQCRec A where Workday >= '2004-08-01'
group by Workday ,ID_Machine
order by Workday , ID_Machine
2004-08-01 3 82 13 .20630215 .2063021519543258673 .1585365853658536585
2004-08-01 10 14 2 .14285714 .1428571428571428571 .1428571428571428571
2004-08-01 11 14 3 .21428571 .2142857142857142857 .2142857142857142857
2004-08-01 12 54 12 .19166666 .1916666666666666666 .2222222222222222222
2004-08-01 13 35 5 .14855072 .1485507246376811593 .1428571428571428571
2004-08-01 14 32 5 .19444444 .1944444444444444444 .1562500000000000000從結果可看出:
AVG(Proportion_NG) as 不良率1,
AVG(cast(Count_NG as numeric )/ cast(Count_QC as numeric)) 不良率2,
結果是一樣的,但
cast(Sum( Count_NG) as numeric)/cast(Sum(Count_QC) as numeric) as 不良率3
結果卻大部分不一樣,數學理論上這些結果應該是一樣的,可誤差太大。懷疑不是誤差造成的原因哪位有興趣的可留下郵箱,我把表導出。
AVG(A/B) = sum(A/B)/n != sum(A)/sum(B)