mysql 难点理解
1.在雇员表里找出每个组里面薪资排名第二的人
SELECT e1.*,COUNT(e2.sal)+1 cs
-- SELECT e1.*,e2.ename,e2.sal
FROM emp e1
LEFT JOIN emp e2
ON e1.deptno=e2.deptno AND e1.sal < e2.sal
-- order BY e1.deptno,e1.empno
GROUP BY e1.deptno,e1.empno
HAVING cs=2
2.找出表里面重复的数据 并删除最大的
DELETE person
-- SELECT person.*
FROM person ,(SELECT * FROM person ) p2
WHERE person.Email =p2.Email AND person.Id>P2.Id
**注意点:**在updata和select共用的时候,select弄出的表必须作为一张视图。
3.查找每个学课成绩的中间数
SELECT st.*,sc.c_id,sc.s_score,COUNT(s.s_score)+1 cs
-- SELECT *
-- SELECT sc.c_id,COUNT(s.s_score)+1 cs,sc.s_score
FROM Score sc
LEFT JOIN Student st
ON st.s_id = sc.s_id
LEFT JOIN Score s
ON sc.c_id = s.c_id AND sc.s_score < s.s_score
GROUP BY sc.s_id,sc.c_id
HAVING (sc.c_id,cs) IN (
SELECT c_id,FLOOR((COUNT(*)+1)/2) cs
FROM Score
GROUP BY c_id
);
求排名:求比他高的数,连接找分数大于他的
聚合函数是在分组后进行处理
4.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT s.*
-- select *
FROM student s
LEFT JOIN score sc1
ON s.`s_id`=sc1.`s_id` AND sc1.`c_id`='01'
LEFT JOIN score sc2
ON s.`s_id`=sc2.`s_id` AND sc2.`c_id`='02'
WHERE sc1.`c_id`IS NOT NULL AND sc2.`c_id`IS NOT NULL
;
5.查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- SELECT *
-- FROM Student
-- WHERE s_id IN(
-- SELECT s_id
-- FROM Score
-- GROUP BY s_id
-- HAVING GROUP_CONCAT(c_id) = (
-- SELECT GROUP_CONCAT(c_id)
-- FROM Score
-- GROUP BY s_id
-- HAVING s_id = 01
-- )
-- AND s_id <> '01'
-- )
select * from Student where s_id in (
select distinct(c.s_id) from Score c
where c.c_id in (select c_id from score where s_id='01') and c.s_id!='01'
group by s_id
having count(1)=(select count(1) from score where s_id='01'))