一条SQL查询出每门课程的成绩都大于80的学生姓名是一道有歧义的题吗?

‘用一条SQL语句查询出每门课程的成绩都大于80的学生姓名’是一道歧义题吗?

name   kecheng    fenshu 
张三     语文             81
张三     数学             90
李四     语文             76
李四     数学             90
王五     语文             81
王五     数学             100
王五     英语             90
用一条SQL语句查询出每门课程的成绩都大于80的学生姓名。

这时一道比较经典的数据库方面的面试题,经常出现在各种面试笔试中,主要考察应试者数据库功底。

这道题有两种理解思路:

第一种是出现在表里面的课程就是该人的所有课程,即张三李四在没有英语成绩下,两门都大于80也是算每门都大于80。

第二种是他们都有英语数学语文三门课程,没有出现在表的成绩要按0来算,即张三李四还有一门成绩为0的英语。

我查阅一些相关的资料,发现大家都是以第一种来编程的,下面我来写下这两种的相关SQL。
至于那种是正确的凭君来断,亦或留下你的见解。

第二种的正确的思路是:查询出某个人的最小成绩是否大于80并且这个人的课程数是3门。

1、用not in 或者 not exists 实现 第一种的写法 不过DISTINCT关键字尽量少用,效率太低

	//not in 
	SELECT DISTINCT A.name FROM Student A 
	WHERE A.name not in(
	SELECT Distinct S.name FROM Student S WHERE S.score <80);

	//not exists
	SELECT DISTINCT A.name From Student A  
	where not exists (SELECT 1 From Student S Where  S.score <80 AND S.name =A.name);
第二种通俗写法,效率不高:
	select distinct A.Name
	  from table1 A
	 where a.name not in (select u.name
					from (select s.name,
					        s.fenshu,
						count(s.name) over(partition by s.name) count1
						from table1 s) u
				 where u.fenshu < 80
				or count1 < 3);	

2、第一种写法是,效率比较高:

	SELECT S.name
	FROM Student S
	GROUP BY S.name
	Having MIN(S.score)>=80 ;
在上面的那些数据确实可以得到王五,但是当张三或者李四语文数学都是大于80的时候,就会查询出来所有的名字。

第二种效率高的写法:

	select A.Name
	  from table1 A
	 group by A.Name
	having count(1) = 3 and min(a.fenshu) >= 80;
另外一种等价的写法:
	select t.name
	  from (select d.name,
			d.kecheng,
			(case
			when to_number(d.fenshu, '990.99') >= 80 then
			 1
			else
			 0
			 end) cnt
			  from table1 d) t
	 group by t.name
	having sum(t.cnt) >= 3;

猿友们你觉得那种是正确的思路那?请不吝点评,共同探讨。


每天努力一点,每天都在进步。
©️2020 CSDN 皮肤主题: 终极编程指南 设计师:CSDN官方博客 返回首页