代码之家 › 专栏 › 技术社区 › xingbin

选择既选择足球又选择篮球的学生

group-by sql

xingbin · 技术社区 · 7 年前

我有一张桌子 course 这样地:

studentName  courseName

 Alen        basketball
 Alen        football
 Alen        tennis
 Bob         tennis
 Dean        football
 Charlie     football
 Charlie     basketball

我想选择哪个学生 二者都 篮球和足球。

预期产量:

studentName  courseName

 Alen        basketball
 Alen        football
 Alen        tennis
 Charlie     football
 Charlie     basketball

我怎样才能有效地做到这一点?

我当前正在使用此sql。它运行良好,但似乎很慢:

select * from course as ss1 where 
exists 
(select * from course as ss2 where ss2.studentName = ss1.studentName and ss2.courseName = 'basketball')
and exists
(select * from course as ss2 where ss2.studentName = ss1.studentName and ss2.courseName = 'football')
order by ss1.studentName desc

4 回复 | 直到 7 年前

an33sh 7 年前

而不是使用 EXIST 要获取和比较相交数据,可以尝试使用 JOIN .

样本 sql-server 在下面。

SELECT wholeStudents.*
FROM course wholeStudents
JOIN
  (SELECT DISTINCT StudentName
   FROM course
   WHERE CourseName IN ('basketball', 'football')
   GROUP BY StudentName 
   HAVING COUNT(1) =2) 
   AS disticnctStudents -- this table will only have a list of students selected both sports
ON (disticnctStudents.StudentName = wholeStudents.StudentName)

注意 :假设studentName是唯一的,一个学生只能进行一次运动。

Gireesh 7 年前

select ss1.studentName, ss1.courseName c1, ss2.courseName c2 from course as ss1 
LEFT JOIN course as ss2 on ss1.studentName = ss2.studentName
where c1 != c2 and (c1 ='basketball' and c2 ='football')

选择学生姓名

select ss1.studentName from course as ss1 
LEFT JOIN course as ss2 on ss1.studentName = ss2.studentName
where ss1.courseName != ss2.courseName and (ss1.courseName ='basketball' and 
ss2.courseName ='football')

Murad Alm. 7 年前

从courseName=“basketball”或courseName所在的课程中选择*= “足球”

简朴万岁xD

Navid H 7 年前

您可以使用两个简单的查询,然后找到两者的交集

SELECT studentName FROM course WHERE courseName = 'basketball'
INTERSECT
SELECT studentName FROM course WHERE courseName = 'football'

看这个给博士后看 http://www.postgresqltutorial.com/postgresql-intersect/

或者这个给甲骨文 https://www.techonthenet.com/oracle/intersect.php

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

5 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

6 月前

John D · 需要为NULL或NOT NULL的WHERE子句

6 月前

ojek · 如何对SQL结果进行分组和编号?

6 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

6 月前

Sax · 规范化Google表格(第一步)

6 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

6 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

6 月前

Andrus · 如何在sql中查找第二个匹配项

6 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

6 月前