用一条SQL语句 查询出每门课都大于80分的学生姓名，表(MarkList)如下：

[try23]

用一条SQL语句 查询出每门课都大于80分的学生姓名，表(MarkList)如下：

Name Course Mark
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 英语 100
王五 语文 81
…… …… ……

今天的面试题目,我是按下面写的,在查询中可以出来结果.但主考官说,这样不好,有更好的办法,他没告诉我,特此来求答案,谢谢.

SELECT DISTINCT Name
FROM MarkList
WHERE (Name IN
(SELECT name
FROM marklist
WHERE mark > 80))

[try23]

在此感谢各位回答,特别是Lyw110 ,这种办法真简洁,佩服.

[fcuandy]

若考滤数据完整性,这语句还需改进.

比如,张三有"化学"这门科的成绩,李四没参加考试,那这里就存在 李四没有化学科的成绩记录的情况.

所以要先 (select distinct 科目 from 表) x
每个人的记录与 x 左连,没有某科成绩的用isnull替换为0 ,然后再用 not exists处理才是准确的
(当然,如果每个人都没参加'化学'这门科的考试, 也就是说,每个人都逃了一门课的考试,这个实际中不太现实, 如果确实有这种情况, 那么 select distinct 科目 from 表 得到的结果不能表述所有的科目, 还需要建立科目表)

[yfcomeon]

select distinct name from tb a where not exists(select * from tb where a.name=name and mark<80)

[zhou__zhou]

DECLARE @test table (
Name varchar(10),
Course varchar(10),
Mark float)
insert into @test
select '张三 ', '语文 ', 81 union
select '张三 ', '数学 ', 75 union
select '李四 ', '语文 ', 76 union
select '李四 ', '数学 ', 90 union
select '王五 ', '英语 ', 100 union
select '王五 ', '语文 ', 81
SELECT * FROM @test
/*查询出每门课都大于80分的学生姓名??*/
SELECT DISTINCT NAME FROM @test WHERE Mark>80

[fcuandy]

要是不考滤逃考某课的问题,不用not exists的一种写法:

DECLARE @test table ( 
Name varchar(10), 
Course varchar(10), 
Mark float) 
insert into @test 
select   '张三  ',   '语文  ', 81 union 
select   '张三  ',   '数学  ', 75 union 
select   '李四  ',   '语文  ', 76 union 
select   '李四  ',   '数学  ', 90 union  
select   '王五  ',   '英语  ', 100 union  
select   '王五  ',   '语文  ', 81  

SELECT NAME FROM @test GROUP BY name HAVING count(*)=count(case when mark>=80 then 1 else null end)
--或者
SELECT NAME FROM @test GROUP BY name HAVING count(*)=sum(case when mark>=80 then 1 else 0 end)

[f05982]

select name,min(mark) from Marklist group by name having min(mark)>80

[f05982]

再簡化一下
select name from Marklist group by name having min(mark)>80

[alan chen 1315333 qq]

我觉得wzy_love_sly 的好
先找出至少一科的分数低于80的学生姓名，再排除

[alan chen 1315333 qq]

楼主自己查询的结果不正确

[Colin_Sun]

服了，我昨天面试的时候，也遇到了这个题目，咱俩不会去的同一公司吧！
我当时是这么回答的：
select name from marklist mal
where mark>80
group by name
having count(*)=(select count(*) from marklist where name=mal.name)
不知道这样写好不好？

[Colin_Sun]

仔细一看，lz的代码是不正确的，这样的查询结果是：至少一门功课在80分以上的学生！

[Colin_Sun]

f05982和Lyw110的方法都比我写的强，学到了！

[cxmcxm]

select distinct name
from marklist a
where not exists(select * from marklist where name=a.name and mark<=80)

[jadeaccp]

select distinct 姓名 from 成绩表 group by 姓名 having min(成绩)>80

[秒华]

[Quote=引用 1 楼 lyw110 的回复:]
create table #test
(
Name varchar(10),
Course varchar(10),
Mark float
)

insert into #test
select '张三', '语文', 81 union
select '张三', '数学', 75 union
select '李四', '语文', 76 union
select '李四', '……
[/Quote]


这个好像不完整，如果有人没有选课，没有成绩，那他也会被选出来，这实际上是跟要求违背了吧？

[秒华]

我看书上说”由Exists引出的子查询，其目标列表达式通常为 *”，为什么楼上各位的都不是呢？什么原因啊。

[abeast00]

select distinct name from table  where  name not in (select distinct name from table where fenshu<=80)

[ansensp]

按姓名进行分组查询 然后指定条件 分组中分数的最小值都大于80的
select Name from MarkList group by Name having min(Mark) > 80

[追猫的兔子]

学习了

[nyjxyy]

select distinct name from A
minus
select name from A
where fenshu<=80