mysql五十题及作答

补充：
题目描述
查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL, -- '入职时间'
PRIMARY KEY (emp_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL, -- '一条薪水记录开始时间'
to_date date NOT NULL, -- '一条薪水记录结束时间'
PRIMARY KEY (emp_no,from_date));

解：select a.emp_no,(a.salary-b.salary)as growth from

(select emp_no,salary from salaries where to_date='9999-01-01') as a join

(select s.emp_no,s.salary from salaries s inner join employees e on e.emp_no=s.emp_no and e.hire_date=s.from_date)as b

on a.emp_no=b.emp_no order by growth

原50题：
——————————————————————————————————
题目及表：https://blog.csdn.net/fashion2014/article/details/78826299/

解：1、

SELECT

t1.s_id as id,t1.s_name as name,t1.s_birth as birth ,t1.s_sex as sex,t2.*

FROM

Student AS t1

INNER JOIN

(SELECT

s1.s_id,

s1.c_id AS cid_01,

s2.c_id AS cid_02,

s1.s_score AS score_01,

s2.s_score AS score_02

FROM

score AS s1

INNER JOIN score AS s2 ON s1.s_id = s2.s_id AND s1.c_id = 01

AND s2.c_id = 02

AND s1.s_score > s2.s_score) t2 ON t1.s_id = t2.s_id

2、

SELECT

t1.s_id as id,t1.s_name as name,t1.s_birth as birth ,t1.s_sex as sex,t2.*

FROM

Student AS t1

INNER JOIN

(SELECT

s1.s_id,

s1.c_id AS cid_01,

s2.c_id AS cid_02,

s1.s_score AS score_01,

s2.s_score AS score_02

FROM

score AS s1

INNER JOIN score AS s2 ON s1.s_id = s2.s_id AND s1.c_id = 01

AND s2.c_id = 02

AND s1.s_score < s2.s_score) t2 ON t1.s_id = t2.s_id

4、

(select s_id as id,s_name as name,0 as avg from Student where s_id not in (select distinct s_id from score))

union

(select t1.s_id as id,t2.s_name as name ,t1.avg as avg from (select s_id,AVG(s_score) as avg from score group by s_id having avg<60 ) as t1 left join student as t2 on t1.s_id=t2.s_id)

order by id;

5、

SELECT

t1.s_id, t1.s_name, COUNT(t2.c_id), SUM(t2.s_score)

FROM

student AS t1

LEFT JOIN

score AS t2 ON t1.s_id = t2.s_id

GROUP BY t1.s_id‌

6、

SELECT

count(t_id)

FROM

teacher where t_name like '李%'

7、

SELECT

s.*

FROM

Student AS s

INNER JOIN

score AS sc ON s.s_id = sc.s_id

AND sc.c_id = (SELECT

c_id

FROM

course

WHERE

t_id = (SELECT

t_id

FROM

teacher

WHERE

t_name = '张三'))

8、

select * from student where s_id not in (SELECT

s.s_id

FROM

Student AS s

INNER JOIN

score AS sc ON s.s_id = sc.s_id

AND sc.c_id = (SELECT

c_id

FROM

course

WHERE

t_id = (SELECT

t_id

FROM

teacher

WHERE

t_name = '张三')))

9、

select * from student where s_id in(SELECT

t1.s_id

FROM

company.score t1

INNER JOIN

score t2 ON t1.s_id = t2.s_id AND t1.c_id = 01

AND t2.c_id = 02)

10、

select * from student where s_id=(select s_id from (select * from score where s_id not in (select distinct s_id from score where c_id = 02 )) as t where c_id=01)

11、

select student.s_id,student.s_name,count(score.c_id) from student left join score on student.s_id =score.s_id group by student.s_id having count(score.c_id)<(select count(*) from course)

12、

select * from student where s_id in(select distinct s_id from score where c_id in( select c_id from score where s_id=01) )

13、

select * from student where s_id in (SELECT

s_id

FROM

score

GROUP BY s_id

HAVING GROUP_CONCAT(c_id) IN (SELECT

GROUP_CONCAT(c_id)

FROM

score

WHERE

s_id = 01

GROUP BY s_id))

14、

SELECT

FROM

student

WHERE

s_id NOT IN (SELECT

s_id

FROM

score

WHERE

c_id = (SELECT

course.c_id

FROM

course

INNER JOIN

teacher ON course.t_id = teacher.t_id

AND teacher.t_name = '张三'))

15、

SELECT

t1.s_id, t1.s_name, t2.avg

FROM

student AS t1

INNER JOIN

(SELECT

s_id, round(AVG(s_score)) AS avg

FROM

company.score

WHERE

s_score < 60

GROUP BY s_id

HAVING COUNT(c_id) >= 2) AS t2 ON t1.s_id = t2.s_idd = t2.s_id

select s.*, t.c_id,t.s_score from student s inner join (select * from score where s_score<60 and c_id=01) as t on s.s_id=t.s_id order by s_score desc

17、

SELECT

a.s_id,

(SELECT

s_score

FROM

score

WHERE

s_id = a.s_id AND c_id = '03') as '英语',

(SELECT

s_score

FROM

score

WHERE

s_id = a.s_id AND c_id = '02') as '数学',

(SELECT

s_score

FROM

score

WHERE

s_id = a.s_id AND c_id = '01') AS '语文',

round(avg(s_score)) as 'avg',group_concat(s_score)

FROM

score a group by a.s_id order by 'avg' desc ;

18：

SELECT

c.c_name,

MAX(s.s_score),

MIN(s.s_score),

ROUND(AVG(s.s_score), 2),

CASE

WHEN ROUND(AVG(s.s_score), 2) < 60 THEN '不及格'

WHEN

ROUND(AVG(s.s_score), 2) >= 60

AND ROUND(AVG(s.s_score), 2) < 80

THEN

'中等'

WHEN

ROUND(AVG(s.s_score), 2) >= 80

AND ROUND(AVG(s.s_score), 2) < 90

THEN

'良好'

ELSE '优秀'

END AS '等级'

FROM

score s

INNER JOIN

course c ON s.c_id = c.c_id

GROUP BY s.c_id

19、

SELECT *,rank() over(order by s_score) as 'rank',row_number() over(order by s_score) as 'row_num_rank' FROM company.score;

20、

select s_id,sum(s_score),dense_rank() over(order by sum(s_score) DESC) AS 'RANK' from score group by s_id

21、

select s.c_id as '课程id',c.t_id as '教师id',t.t_name as '教师name',round(avg(s.s_score),2) as '平均分' from score s inner join course c on s.c_id=c.c_id left join teacher t on t.t_id=c.t_id group by s.c_id order by avg(s.s_score) desc

22、

select t1.*,t2.c_id,t2.s_score,t2.rank1 from (select * from (select *,dense_rank() over(partition by c_id order by s_score desc ) as 'rank1' from score ) as t where rank1 in (2,3)) as t2 left join student t1 on t1.s_id=t2.s_id

25、

select * from (select *,row_number() over(partition by c_id order by s_score desc) as r from score) as t2 where r in(1,2,3)

26、SELECT c_id,count(s_id) FROM company.score group by c_id;

27. select s.* from (SELECT s_id,count(c_id) FROM company.score group by s_id having count(c_id)=2) as t left join student s on s.s_id=t.s_id

28、select s_sex,count(1) from student group by s_sex

29、select * from student where s_name like '%风%';

30、select *,count(1) from student as t1 inner join student as t2 on t1.s_name=t2.s_name and t1.s_id!=t2.s_id

32、

select c_id,avg(s_score) as 'avg' from score group by c_id order by avg(s_score) desc , c_id asc

33、select s.*,t.Avg from student s right join (select s_id ,avg(s_score) as Avg from score group by s_id having avg(s_score)>85 ) as t on s.s_id=t.s_id

SELECT

st.*,

(SELECT

s_score

FROM

score

WHERE

c_id = 01 AND s_id = sc.s_id) AS '语文',

(SELECT

s_score

FROM

score

WHERE

c_id = 02 AND s_id = sc.s_id) AS '数学',

(SELECT

s_score

FROM

score

WHERE

c_id = 03 AND s_id = sc.s_id) AS '英语'

FROM

score sc

RIGHT JOIN

student st ON st.s_id = sc.s_id

LEFT JOIN

course co ON co.c_id = sc.c_id

GROUP BY s_id

35、

select st.s_name ,c.c_name,score.s_score from score left join student as st on st.s_id=score.s_id left join course as c on score.c_id=c.c_id where s_score>=70

36、select st.s_name ,c.c_name,score.s_score from score left join student as st on st.s_id=score.s_id left join course as c on score.c_id=c.c_id where s_score<60

40、select student.*,t1.s_score from student inner join (select * from score where c_id =(select c.c_id from teacher t inner join course c on t.t_id=c.t_id where t.t_name='张三') order by s_score desc limit 1) as t1 on student.s_id=t1.s_id

41、

select distinct * from score t2 inner join score as t1 on t1.s_score=t2.s_score and t1.s_id!=t2.s_id and t1.c_id!=t2.c_id

42、select * from (select *,row_number() over(partition by c_id order by s_score desc) as r from score ) as t where r in (1,2)

43、select *,count(1) from score group by c_id having count(1)>5 order by count(1) desc ,c_id

Asc

44、select s_id,count(1) as count from score group by s_id having count(1)>=2

45select s_id,count(1) as count from score group by s_id having count(1)=(select count(*) from course)

46、select s_birth,((date_format(now(),'%Y')-date_format(s_birth,'%Y'))-(case when (date_format(now(),'%m')>date_format(s_birth,'%m')) then 0 else 1 end)) as age

from student;

47、select * from student where week(date_format(now(),'%Y-%m-%d'))=week(s_birth)

48、select * from student where week(date_format(now(),'%Y-%m-%d'))+1=week(s_birth)

49、

select * from student where month(date_format(now(),'%Y-%m-%d'))=month(s_birth)

50、

select * from student where month(date_format(now(),'%Y-%m-%d'))+1=month(s_birth)