【求助】access的資料表資料篩選問題



贊助商連結


shung0116
2004-08-13, 10:57 PM
ex:
欄位: a1 a2 a3 a4 a5 a6
值: A 1 ab-1 1 NG 1.8
A 1 ab-1 2 PASS 2.4
A 1 ab-1 4 PASS 3.2
A 2 cd 3 PASS 3.6
A 2 cd 5 PASS 2.2
B 1 ab PASS 3.6
B 1 ab 1 PASS 4.6
條件:以a1,a2,a3為群組,a4欄位一定要有值,a5欄位則不為'NG',求a6的平均。
難題:假設同一群組中,若有一筆a5欄位為'NG',比方上表的前面1-3筆,雖然都是同一群組,且a4欄位均有值,但因為第一筆資料的a5欄位為NG,所以這三筆都不符合條件(篩選後要踢除),同樣的 B的這兩筆資料中,雖然一樣也是在同一群組中,且a5欄位均為PASS,但因為a4欄位裡,有一筆資料是空的,所以同樣的兩筆也不符合條件,最後篩選的結果,因該只剩下一筆資料,如下所述:
欄位: a1 a2 a3 number
A 2 cd 2.9

我的寫法:
select a1,a2,a3,avg(a6) as number from (select * from tablename where a4<>'' and a5<>'NG') group by a1,a2,a3

但是這樣的問題就是:它會誤把1-3筆內的第2及第3筆也算進去,第七筆也算進去,但是實際上它們是不用算進去的
請各位幫忙,因為已經試了很久了,還找不出所以然
謝謝

贊助商連結


htkstw
2004-08-13, 11:48 PM
你看這樣行不行, 按照你給的資料, 我試過是可以.

SELECT tablename.a1, tablename.a2, tablename.a3, AVG(tablename.a6) AS [Number]
FROM tablename INNER JOIN [SELECT a1 AS a11,a2 AS a12,a3 AS a13 FROM tablename WHERE ISNULL(a4) OR a4='' OR a5='NG' GROUP BY a1,a2,a3]. AS k ON (k.a13<>tablename.a3) AND (k.a12<>tablename.a2) AND (k.a11<>tablename.a1)
GROUP BY tablename.a1, tablename.a2, tablename.a3;

shung0116
2004-08-14, 04:17 PM
感謝您的回覆,我實際測試後結果變為0筆,但實際上應該會有1筆
我參考您的寫法如下:
SELECT guest.MV.F4, guest.MV.F8, guest.MV.F9, AVG(guest.MV.F32) AS number FROM guest.MV INNER JOIN (SELECT f4 AS f14, f8 AS f18, f9 AS f19 FROM [guest].[mv] WHERE guest.mv.f11 <> '' AND guest.mv.f31 <> 'NG' GROUP BY f4,f8,f9) b ON b.f14 <> guest.MV.F4 AND b.f18 <> guest.MV.F8 AND b.f19 <> guest.MV.F9 GROUP BY guest.MV.F4, guest.MV.F8, guest.MV.F9

測試環境在access或sql中均是同樣的結果:0筆
guest.mv為資料表(sql中的表示方式:擁有者.資料表名)

謝謝

htkstw
2004-08-14, 09:33 PM
(SELECT f4 AS f14, f8 AS f18, f9 AS f19 FROM [guest].[mv] WHERE guest.mv.f11 <> '' AND guest.mv.f31 <> 'NG' GROUP BY f4,f8,f9)
這一段 WHERE guest.mv.f11 <> '' AND guest.mv.f31 <> 'NG'

應該是 WHERE isnull(guest.mv.f11) OR guest.mv.f11 <> '' OR guest.mv.f31 <> 'NG'

應該 OR 不是 AND, 改成 AND 後, 其意義就差了十萬八千里了, 相信你一定知道.

另外, 不知道你用的是哪一種sql語法? 我使用的 ms access, 測試環境是 ms office xp 的 access, 所以可以用 isnull() 的函數.

在這樣的使用環境下, isnull() 是很有用的, 因為如果字串欄位無值, 它是null值, 用 isnull() 可以測試是否為 null 值. 例如你前面文章中的 B 1 ab PASS 3.6 這筆資料, f4的欄位即為 null 值, 如此在建立捨棄資料的篩選前置作業中, 就無法被篩選掉.

最後, 我又重新以你寫的欄位名稱試了一下, 結果還是可行的.

shung0116
2004-08-15, 05:50 PM
感謝您的回覆,經我的測試後結果還是0筆
附件我把我的查詢加入,測試環境是在office 2000 access or SQL SERVER 2000
謝謝