大家好！情況是這樣的：
表格（第一張大圖）有ID, MP__ID, Tiefe_m, TempWert_oC 和ELWert_mScm 五列，分別是表tblGwMeasurement 的主鍵列，測量記錄編號，深度，溫度和電導率。現在想通過在access中撰寫一個sql代碼，將深度分段（0~5m,5~10m...145~150m), 并在每一個深度段下面篩選出對應的溫度值和電導率值（第二張小圖）。這是最好的一步直達的設想，但是我沒有能夠撰寫出來，所以想請論壇里的大神們幫幫忙。
如果這個難以實作的話，是否有辦法一次性通過類似iif()或者switch()函式在原表tblGwMeasurement的基礎上再加一列DepthInterval，對每一個深度添加一個深度段？然后再通過合并表格，篩選出每個深度段對應的溫度值們或者電導率們。下面是我自己寫的代碼，但是由于iff()和switch()都只能寫十幾個，而我有29個條件。所以我得分成三次做成三個表。但是我覺得，這樣肯定會被老師給砍死。
SELECT tblGwMeasurement.ID, tblGwMeasurement.MP_ID, tblGwMeasurement.Tiefe_m, tblGwMeasurement.TempWert_oC, tblGwMeasurement.ELWert_mScm,
IIf(0<=Tiefe_m And Tiefe_m<5,'0-5muGOK',
IIf(5<=Tiefe_m And Tiefe_m<10,'5-10muGOK',
IIf(10<=Tiefe_m And Tiefe_m<15,'10-15muGOK',
IIf(15<=Tiefe_m And Tiefe_m<20,'15-20muGOK',
IIf(20<=Tiefe_m And Tiefe_m<25,'20-25muGOK',
IIf(25<=Tiefe_m And Tiefe_m<30,'25-30muGOK',
IIf(30<=Tiefe_m And Tiefe_m<35,'30-35muGOK',
IIf(35<=Tiefe_m And Tiefe_m<40,'35-40muGOK',
IIf(40<=Tiefe_m And Tiefe_m<45,'40-45muGOK',
IIf(45<=Tiefe_m And Tiefe_m<50,'45-50muGOK',
IIf(50<=Tiefe_m And Tiefe_m<55,'50-55muGOK',
IIf(55<=Tiefe_m And Tiefe_m<60,'55-60muGOK',
IIf(60<=Tiefe_m And Tiefe_m<65,'60-65muGOK',Null)))))))))))))
AS DepthInterval INTO tblDepthInterval0_65
FROM tblGwMeasurement;
SELECT tblGwMeasurement.ID, tblGwMeasurement.MP_ID, tblGwMeasurement.Tiefe_m, tblGwMeasurement.TempWert_oC, tblGwMeasurement.ELWert_mScm,
IIf(65<=Tiefe_m And Tiefe_m<70,"65-70muGOK",
IIf(70<=Tiefe_m And Tiefe_m<75,"70-75muGOK",
IIf(75<=Tiefe_m And Tiefe_m<80,"75-80muGOK",
IIf(80<=Tiefe_m And Tiefe_m<85,"80-85muGOK",
IIf(85<=Tiefe_m And Tiefe_m<90,"85-90muGOK",
IIf(90<=Tiefe_m And Tiefe_m<95,"90-95muGOK"，
IIf(95<=Tiefe_m And Tiefe_m<100,"95-100muGOK",
IIf(100<=Tiefe_m And Tiefe_m<105,"100-105muGOK",
IIf(105<=Tiefe_m And Tiefe_m<110,"110-115muGOK",
IIf(110<=Tiefe_m And Tiefe_m<115,"110-115muGOK",
IIf(115<=Tiefe_m And Tiefe_m<120,"115-120muGOK",
IIf(120<=Tiefe_m And Tiefe_m<125,"120-125muGOK",
IIf(125<=Tiefe_m And Tiefe_m<130,"125-130muGOK",Null)))))))))))))
AS DepthInterval INTO tblDepthInterval65_130
FROM tblGwMeasurement;
SELECT tblGwMeasurement.ID, tblGwMeasurement.MP_ID, tblGwMeasurement.Tiefe_m, tblGwMeasurement.TempWert_oC, tblGwMeasurement.ELWert_mScm,
IIf(130<=Tiefe_m And Tiefe_m<135,"130-135muGOK",
IIf(135<=Tiefe_m And Tiefe_m<140,"135-140muGOK",
IIf(140<=Tiefe_m And Tiefe_m<145,"140-145muGOK",Null)))
AS DepthInterval INTO tblDepthInterval130_150
FROM tblGwMeasurement;
實在是不好意思， 這個問題看起來有點煩雜。 再次謝過大家了！
uj5u.com熱心網友回復：
你這個分院是由規律的嘛uj5u.com熱心網友回復：
SELECT * , CONVERT(VARCHAR,TEAM*5) + '~' + CONVERT(VARCHAR,TEAM*5+5) FROM (
SELECT * , CAST (FLOOR(ISNULL(Tiefe_m,0)/5) AS VARCHAR) TEAM FROM tblGwMeasurement
) Z
ID MP__ID Tiefe_m TempWert_oC ELWert_mScm TEAM
------------------------ ------------------------ --------------------------------- --------------------------------- --------------------- ------------
1 MP1 2.23 10 .513 0 0~5
2 MP2 4.23 10 .513 0 0~5
3 MP3 6.23 10 .513 1 5~10
4 MP4 8.23 10 .513 1 5~10
5 MP5 10.23 10 .513 2 10~15
6 MP6 12.23 10 .513 2 10~15
uj5u.com熱心網友回復：
你先看有沒有規律，如果有規律上面的就可以了轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/85461.html
標籤：Access
上一篇：求助！！！create database 顯示 not connected!
下一篇：觸發器 各種語法