作者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