SQL 找出平均成绩大于80的学生总人数?找出平均成绩最高的学生的所有信息?

1.select count(*) from (select 学号,avg(成绩) score from grade group by 学号) a where a.score>80
2.select * from student where 学号 in(select top 1 学号 from (select 学号,avg(成绩) score from grade group by 学号)a order by score desc)

哦，第二个没注意，已修改

第一题：
SELECT count(number)
FROM dbo.[GRADE]
WHERE number in
(SELECT number, avg(grade)
FROM dbo.[grade]
group by number
having avg(grade)>80
)
group by number

第二题：

SELECT 学号，姓名，班号，课程号，成绩
FROM dbo.[GRADE]，dbo.[STUDENT]
WHERE GRADE.number=STUDENT.number and
number in
(SELECT number, max(grade)
FROM dbo.[grade]
group by number
)

楼主试试吧~呵呵，好久没写过数据库了，也不知道行不行

SELECT count(number)
FROM dbo.[GRADE]
WHERE number in
(SELECT number, avg(grade)
FROM dbo.[grade]
group by number
having avg(grade)>80
) group by number

第二个问题:
select p1.number,p1.name,p2.clnumber,p1.conumber,grade from (selct number,name,conumber,grade from grade) p1 left join (select number,clnumber from STUDENT) p2 on p1.number=p2.number where number= (SELECT number,max(grade) FROM GRADE WHERE number in (SELECT number, avg(grade) FROM grade group by number having avg(grade)>80))

新修改的语句,经过测试.....

SELECT count(*)
FROM
(SELECT avg(grade) as column1
FROM dbo.[grade]
group by number
having avg(grade)>80
) as t