查询和" 01 “号的同学学习的课程 完全相同的其他同学的信息
– 不存在这样的课程 , 01学了,但是学生x没学 -> 蕴含逻辑运算
– (像这种蕴含逻辑运算,涉及到离散,做一些简单倒是可以,比如"完全相同”,“没有全部拥有的"等全量字眼)
SELECT *
FROM student
WHERE NOT EXISTS(
SELECT *
FROM stucou sc1
WHERE sc1.SId='01' AND NOT EXISTS(
SELECT *
FROM stucou sc2
WHERE sc2.SId=student.SId AND sc1.CId=sc2.CId)
);
详解:https://blog.csdn.net/qsyzb/article/details/12525955
查询学过"张三"老师讲授的全部课程的学生姓名
– 不存在这样的课程,张三老师教了,但是学生没学
SELECT
s.*
FROM student s
WHERE NOT EXISTS (
SELECT 1 FROM teacher t
INNER JOIN course c ON c.TId= t.TId
WHERE t.Tname='张三' AND NOT EXISTS (
SELECT 1 FROM stucou sc
WHERE c.CId = sc.CId AND sc.SId=s.SId
)
)
对于employees表中,给出奇数行的first_name
select e.first_name
from employees e
where
(select count(1) from employees e1 where e.first_name >= e1.first_name)%2!=0
对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名,相同salary并列且按照emp_no升序排列
– 排名: 小于自己的有多少个,就是排第几,有点类似计数排序
select s.emp_no,s.salary,count(distinct s1.salary) as rank
from salaries s,salaries s1
where s.to_date='9999-01-01' and s1.to_date='9999-01-01' and s.salary<=s1.salary
group by s.emp_no
order by s.salary desc,s.emp_no asc