作者ferrarirossa (Sean)
看板Database
標題[SQL ] AVG()用法
時間Sun Jul 16 17:20:10 2006
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)
1F：推 PsMonkey:居... 居然給一堆 SQL 語法，實在太不體諒閱讀的人了 07/16 17:24
※ 編輯: ferrarirossa 來自: 61.230.49.66 (07/16 17:33)
2F：推 ferrarirossa:我改過了 這樣方便閱讀了 07/16 17:33
※ 編輯: ferrarirossa 來自: 61.230.49.66 (07/16 17:35)
※ 編輯: ferrarirossa 來自: 61.230.49.66 (07/16 17:43)
3F：推 ferrarirossa:快解了 使用HAVING 07/16 18:22