MySQL 入门实践——「编程题实战」

题目：查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

做法1

SELECT (
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth

做法2

SELECT (a.salary-b.salary) as growth FROM (SELECT salary FROM salaries WHERE emp_no=10001 ORDER BY to_date DESC LIMIT 0,1 ) a,
(SELECT salary FROM salaries WHERE emp_no=10001 ORDER BY to_date LIMIT 0,1 ) b;

---

题目：删除emp_no重复的记录，只保留最小的id对应的记录

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);

分析的原理：
1、使用DELETE FROM TABLE
2、找到需要删除的重复记录或者找到不要删除的记录，排除他们。这边找不需要删除的记录

DELETE FROM titles_test WHERE id not in  
(SELECT min(id) FROM titles_test GROUP BY emp_no);

---

题目：获取有奖金的员工相关信息

重点就是CASE WHEN END的使用

SELECT employees.emp_no,employees.first_name,employees.last_name,emp_bonus.btype,salaries.salary,(CASE emp_bonus.btype
   WHEN 1 THEN salaries.salary*0.1
   WHEN 2 THEN salaries.salary*0.2
   ELSE salaries.salary*0.3
   END) AS bonus
FROM employees INNER JOIN salaries ON salaries.emp_no = employees.emp_no INNER JOIN emp_bonus ON 
emp_bonus.emp_no = salaries.emp_no  WHERE to_date='9999-01-01'

---

查找所有员工自入职以来的薪水涨幅情况

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_noy以及其对应的薪水涨幅growth，并按照growth进行升序
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));

分析：是否能构建表实现？
薪资涨幅=当前工资-入职工资

当前工资: to_date=’9999-01-01’
入职工资: employees.hire_date=salaries.from_date
根据growth升序：ORDER BY growth

SELECT t1.emp_no,(t1.salary-t2.salary) AS growth FROM 
(SELECT emp_no,salary FROM salaries WHERE to_date =  '9999-01-01') AS t1 
LEFT JOIN
(SELECT salaries.emp_no,salaries.salary FROM salaries INNER JOIN employees ON salaries.from_date=employees.hire_date
AND employees.emp_no = salaries.emp_no) AS t2 
ON t1.emp_no=t2.emp_no 
ORDER BY growth;

---

对所有员工的薪水按照salary进行按照1-N的排名

对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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));

注意点：
1. salary是倒序
2. 1-N的排名，同时相同的salary排名一致

分析：
单表要进行排名，可以根据复用表进行排列。
1. salaries s1,salaries s2 两表进行笛卡尔乘积后，筛选当前的工资 to_date=’9999-01-01’
2. 排名即为有多少个薪资在该员工之前，如10010的薪资94409，有一个比他大，一个和他相同。即判断s1.salary<=s2.salary,多少个薪资比其大。同时需要排除重复的薪资，count(DISTINCT s2.salary) AS rank

SELECT s1.emp_no,s1.salary,count(DISTINCT s2.salary) AS rank
 FROM salaries s1,salaries s2 
 WHERE s1.to_date='9999-01-01' AND s2.to_date='9999-01-01' 
 AND s1.salary <=s2.salary   
GROUP BY s1.emp_no ORDER BY s1.salary DESC ,s1.emp_no ASC;

---

查找字符串’10,A,B’ 中逗号’,’出现的次数cnt

参考思路就是通过替换，来判断少了多少个‘,’的关键字。然后算出有多少个‘,’

SELECT (length('10,A,B')-length(REPLACE('10,A,B',',',''))) AS cnt;

---

对于employees表中，给出奇数行的first_name

对于employees表，在对first_name进行排名后，选出奇数排名对应的first_name

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

遍历employees，查找每个first_name的对应的排名，然后判断奇偶数。