mysql 难点SQL题理解

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'))