[SQL ] AVG()用法

看板Database (資料庫)作者ferrarirossa (Sean)時間18年前 (2006/07/16 17:15)推噓3(3推 0噓 0→)

留言3則, 2人參與討論串1/1

SELECT StdSSN FROM Enrollment WHERE OfferNo IN (SELECT OfferNo FROM Course WHERE CourseNo LIKE 'IS%') AND AVG(EnrGrade) > 3.05 以上是錯的語法先在一個TABLE裡找到IS開頭的課程的OfferNo後去對應到Enrollment裡的StdSSN 要印出來但是前提之下就是這個OfferNo的班級的GPA平均值要高過3.05 抓到的OfferNo 有很多個而且在Enrollment裡的每個OfferNo又有很多組成積值要怎麼抓到個別的AVG()呢? 以下是資料內容下面是抓到IS起頭的課程最一開始的欄位就是OfferNo VALUES (1111,'IS320','SUMMER',2006,'BLM302','10:30:00',NULL,'MW') VALUES (1234,'IS320','FALL',2005,'BLM302','10:30:00','098765432','MW') VALUES (2222,'IS460','SUMMER',2005,'BLM412','13:30:00',NULL,'TTH') VALUES (3333,'IS320','SPRING',2006,'BLM214','8:30:00','098765432','MW') VALUES (4321,'IS320','FALL',2005,'BLM214','15:30:00','098765432','TTH') VALUES (4444,'IS320','WINTER',2006,'BLM302','15:30:00','543210987','TTH') VALUES (5678,'IS480','WINTER',2006,'BLM302','10:30:00','987654321','MW') VALUES (5679,'IS480','SPRING',2006,'BLM412','15:30:00','876543210','TTH') VALUES (8888,'IS320','SUMMER',2006,'BLM405','13:30:00','654321098','MW') VALUES (9876,'IS460','SPRING',2006,'BLM307','13:30:00','654321098','TTH') 每個OfferNo一班都有數個學生(中間的值是SSN) 現在要挑出那些OfferNo裡條件是裡面的學生平均有超過3.05 VALUES (1234,'123456789',3.30) VALUES (1234,'456789012',3.10) VALUES (1234,'567890123',3.80) VALUES (1234,'678901234',3.40) VALUES (4321,'123456789',3.50) VALUES (4321,'124567890',3.20) VALUES (4321,'789012345',3.50) VALUES (4321,'876543210',3.10) VALUES (4321,'890123456',3.40) VALUES (4321,'901234567',3.10) VALUES (5555,'123456789',3.20) VALUES (5555,'124567890',2.70) VALUES (5678,'123456789',3.20) VALUES (5678,'234567890',2.80) VALUES (5678,'345678901',3.30) VALUES (5678,'456789012',3.40) VALUES (5678,'567890123',2.60) VALUES (5679,'123456789',2.00) VALUES (5679,'124567890',3.70) VALUES (5679,'678901234',3.30) VALUES (5679,'789012345',3.80) VALUES (5679,'890123456',2.90) VALUES (5679,'901234567',3.10) VALUES (6666,'234567890',3.10) VALUES (6666,'567890123',3.60) VALUES (7777,'876543210',3.40) VALUES (7777,'890123456',3.70) VALUES (7777,'901234567',3.40) VALUES (9876,'124567890',3.50) VALUES (9876,'234567890',3.20) VALUES (9876,'345678901',3.20) VALUES (9876,'456789012',3.40) VALUES (9876,'567890123',2.60) VALUES (9876,'678901234',3.30) VALUES (9876,'901234567',4.00) -- 哇撘~~~~~~~~~~ http://www.wretch.cc/album/ferraricarus -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 61.230.49.66 ※ 編輯: ferrarirossa 來自: 61.230.49.66 (07/16 17:22)