sql:查询所有科目分数都在80分(含)以上的学生姓名

[meteor57]

设tb表如下:
name subject grade

mike math 79
lee chinese 81
lee math 78
tom chinese 85
tom math 81
tom english 88
。。。。

查询所有科目分数都在80分(含)以上的学生姓名

哎，不会做....
特来请教~~~
先谢谢了~

[feixianxxx]

select name
from tb  k
where not exists(select * from tb where k.name=name and grade<80)

[sixer04]

select name from tb where name not in (select name from tb where grade<80)

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------------
   --------------------希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( name  varchar(10),subject varchar(10) ,grade int )
go
insert tb SELECT 
'mike', 'math'    ,79 UNION ALL SELECT 
'lee' , 'chinese' ,81 UNION ALL SELECT 
'lee' , 'math'    ,78 UNION ALL SELECT 
'tom' , 'chinese' ,85 UNION ALL SELECT 
'tom'  ,'math'    ,81 UNION ALL SELECT 
'tom' , 'english' ,88 
go
select distinct name
from tb  k
where not exists(select * from tb where k.name=name and grade<80)

go
name
----------
tom

[SQL77]

SELECT * FROM TB WHERE grade >=80 
HAVING COUNT(*) =(SELECT COUNT(DISTINCT SUBJECT) FROM TB )

[SQL77]

IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( name  varchar(10),subject varchar(10) ,grade int )
go
insert tb SELECT 
'mike', 'math'    ,79 UNION ALL SELECT 
'lee' , 'chinese' ,81 UNION ALL SELECT 
'lee' , 'math'    ,78 UNION ALL SELECT 
'tom' , 'chinese' ,85 UNION ALL SELECT 
'tom'  ,'math'    ,81 UNION ALL SELECT 
'tom' , 'english' ,88 
go

SELECT NAME FROM TB WHERE grade >=80 GROUP BY NAME
HAVING COUNT(*) =(SELECT COUNT(DISTINCT SUBJECT) FROM TB )

（所影响的行数为 6 行）

NAME       
---------- 
tom

（所影响的行数为 1 行）

[guguda2008]

学习的时候都不自己写将来工作了怎么办？

[华夏小卒]

IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( name  varchar(10),subject varchar(10) ,grade int )
go
insert tb SELECT 
'mike', 'math'    ,79 UNION ALL SELECT 
'lee' , 'chinese' ,81 UNION ALL SELECT 
'lee' , 'math'    ,78 UNION ALL SELECT 
'tom' , 'chinese' ,85 UNION ALL SELECT 
'tom'  ,'math'    ,81 UNION ALL SELECT 
'tom' , 'english' ,88 
go


SELECT DISTINCT NAME FROM TB T
WHERE NAME NOT IN(SELECT NAME FROM TB WHERE  GRADE<80)

NAME
----------
tom

(1 行受影响)

[zc_0101]

--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101	At:2009-08-17 20:34:20=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(4),subject varchar(7),grade int)
insert into #T
select 'mike','math',79 union all
select 'lee','chinese',81 union all
select 'lee','math',78 union all
select 'tom','chinese',85 union all
select 'tom','math',81 union all
select 'tom','english',88
 
----------------查询------------
select name,min(grade) 最低分 from #T group by name having min(grade)>80
----------------结果--------------
/*
name 最低分
tom    81
*/

[feihong0233]

/*如果科目是固定的话（当然，你这个看起来就只有三科，貌似小学成绩软件）
命题可以理解为计算成绩大于80分的科目数量为最大数，即3，由此可得*/
SELECT [Name] FROM [tb] 
  WHERE [Grade] >= 80 
  GROUP BY [Name] HAVING COUNT(*) = 3 
  ORDER BY [Name];
GO

[feihong0233]

虽然写成固定科目比较方便理解，不过SQL77的更全面

[zc_0101]

[Quote=引用 8 楼 zc_0101 的回复:]
SQL code--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101 At:2009-08-17 20:34:20=========
--========+++++++++++++++++++++++++++++++¡­
[/Quote]

这个最聪明

[youqi1984]

是的，SQL77比较全面

[feihong0233]

答zc_0101：
你可以试试你例子里

select 'mike','math',81 union all

你选择的是考试科目最低分也高于80的，
但是只有2科成绩或者1科成绩，也就是说缺考的都会被考虑，
这样应该违反搂主所有科目（个人理解是全科80以上的优等生）大于80……

[meteor57]

不好意思,是我没有说清楚,表中,每个人课程不一定相同,只要求所选的课程得分>80分就可以了.

[Quote=引用 8 楼 zc_0101 的回复:]
select name,min(grade) 最低分 from #T group by name having min(grade)>80
[/Quote]谢谢各位了!
这个貌似比较容易理解.
脑子老是转不过来,呵呵~^_^