資料表如何交叉查詢產生資料? |
尚未結案
|
RogerHer
一般會員 發表:11 回覆:39 積分:10 註冊:2002-03-13 發送簡訊給我 |
各位大大,
我有大約七個類似下列的資料表要建立,
每個 Table 都有 Low、Med 及 High 的資料,但縱向的欄位則每個 Table 都不同,我想用一個 Table 儲存七個 Table 的 Low、Med及High 欄位的資料(浮點數),而七個 Table 裏面只存一個索引值(整數)來索引到 Low、Med 、及 High 的值,可是問題來了,這樣我應該如何產生報表?
因為報表都是以一筆資料為單位縱向列印,要嘛就是印 Low、Med、High 的值(應該要橫向列印),要嘛就是印其他七個資料表的內的索引值,弄了快二個星期了都弄不出來,不知道有沒有人有好辦法? 這星期如果再弄不出來只好一個個欄位建了....
|
Brian77
中階會員 發表:8 回覆:114 積分:94 註冊:2002-05-17 發送簡訊給我 |
|
RogerHer
一般會員 發表:11 回覆:39 積分:10 註冊:2002-03-13 發送簡訊給我 |
|
Brian77
中階會員 發表:8 回覆:114 積分:94 註冊:2002-05-17 發送簡訊給我 |
先貼上目前架構時的 SQL
SELECT ID,Hopping,SUM(LowNum) AS Low,SUM(MedNum) AS Med,SUM(MaxNum) AS High FROM ( SELECT B.ID,'1Avg' AS Hopping, A.Avg AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'1Avg' AS Hopping, 0 AS LowNum, A.Avg AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'1Avg' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Avg AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH UNION SELECT B.ID,'2Max' AS Hopping, A.Max AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'2Max' AS Hopping, 0 AS LowNum, A.Max AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'2Max' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Max AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH UNION SELECT B.ID,'3Min' AS Hopping, A.Min AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'3Min' AS Hopping, 0 AS LowNum, A.Min AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'3Min' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Min AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH UNION SELECT B.ID,'4Peak' AS Hopping, A.Peak AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'4Peak' AS Hopping, 0 AS LowNum, A.Peak AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'4Peak' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Peak AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH UNION SELECT B.ID,'5Tested' AS Hopping, A.Tested AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'5Tested' AS Hopping, 0 AS LowNum, A.Tested AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'5Tested' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Tested AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH UNION SELECT B.ID,'6Failed' AS Hopping, A.Failed AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'6Failed' AS Hopping, 0 AS LowNum, A.Failed AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'6Failed' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Failed AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH UNION SELECT B.ID,'7Passed' AS Hopping, A.Passed AS LowNum, 0 AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.Hoponl UNION SELECT B.ID,'7Passed' AS Hopping, 0 AS LowNum, A.Passed AS MedNum, 0 AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponM UNION SELECT B.ID,'7Passed' AS Hopping, 0 AS LowNum, 0 AS MedNum, A.Passed AS MaxNum FROM OP_Results A, OP_Report B WHERE A.ID=B.HoponH ) AS A GROUP BY ID, Hopping |
Brian77
中階會員 發表:8 回覆:114 積分:94 註冊:2002-05-17 發送簡訊給我 |
|
RogerHer
一般會員 發表:11 回覆:39 積分:10 註冊:2002-03-13 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |