資料表如何交叉查詢產生資料? |
尚未結案
|
RogerHer
一般會員 ![]() ![]() 發表:11 回覆:39 積分:10 註冊:2002-03-13 發送簡訊給我 |
各位大大,
我有大約七個類似下列的資料表要建立,
|
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. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |