select [name],count(class) class from course where grade<60 group by [name] having count(class)>=3
select name from gd where grade<60 group by [name] having count(*)>=3
select top 3* from tablename where grade<60 order by grade
select [name] from gd where grade<60 group by [name] having count(*)>=3这个就可以了
Create table counttest(name varchar(10),class char,grade varchar(10)) --select *from counttest insert counttest values('yue',1,55) insert counttest values('yue',2,65) insert counttest values('yue',3,43) insert counttest values('yue',4,28) insert counttest values('long',1,67) insert counttest values('long',2,55) insert counttest values('long',3,45) insert counttrst values('long',4,61) --select *from counttest --work begin Select * from counttest as a where exists( Select name from counttest group by name having count(*)>=3 and name=a.name) and grade>60 /* name class grade yue 2 65 long 1 67 */ --try2: select [name],count(class) class from counttest where grade<60 group by [name] having count(class)>=3 --try3: select top 3* from counttest where grade>=60 order by grade /* name class grade yue 2 65 long 1 67 */
SELECT name FROM 表 where grade<60 GROUP BY name,grade having count(*)>=3
--select *from counttest
insert counttest values('yue',1,55)
insert counttest values('yue',2,65)
insert counttest values('yue',3,43)
insert counttest values('yue',4,28)
insert counttest values('long',1,67)
insert counttest values('long',2,55)
insert counttest values('long',3,45)
insert counttrst values('long',4,61)
--select *from counttest
--work begin
Select * from counttest as a where exists( Select name from counttest group by name
having count(*)>=3 and name=a.name)
and grade>60
/*
name class grade
yue 2 65
long 1 67
*/
--try2:
select [name],count(class) class from counttest where grade<60 group by [name] having count(class)>=3
--try3:
select top 3* from counttest where grade>=60 order by grade
/*
name class grade
yue 2 65
long 1 67
*/