常用sql语句总结2

发表于 | 更新于 | 分类于 | 阅读次数： | 阅读次数：
本文字数： 9.7k | 阅读时长 ≈ 12 分钟

来源于：牛客网
　　

11.从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t：

1
2
3
4
5
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

答：

1
2
SELECT title, COUNT(title) AS t FROM titles 
GROUP BY title HAVING t >= 2

或

1
2
SELECT title, COUNT(emp_no) AS t FROM titles 
GROUP BY title HAVING t >= 2

或

1
2
SELECT title, COUNT(*) AS t FROM titles 
GROUP BY title HAVING t >= 2

解析：
此题应注意以下三点：
1.用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数
2.根据题意，输出每个title的个数为t，故用AS语句将COUNT(title)的值转换为t
3.由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

　　

12.接上题，从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略：

答：

1
2
SELECT title, COUNT(DISTINCT emp_no) AS t FROM titles
GROUP BY title HAVING t >= 2

　　

13.查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列：

1
2
3
4
5
6
7
8
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`));

答：

1
2
3
4
SELECT * FROM employees 
WHERE emp_no % 2 = 1 
AND last_name != 'Mary' 
ORDER BY hire_date DESC;

　　

14.统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg：

1
2
3
4
5
6
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
2
3
4
5
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

答：
方式一：

1
2
3
4
5
6
SELECT t.title AS title, AVG(s.salary) as avg
FROM salaries AS s, titles AS t 
WHERE t.emp_no = s.emp_no
AND t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY title

方式二：

1
2
3
4
5
6
7
SELECT t.title AS title, AVG(s.salary) as avg
FROM salaries AS s 
INNER JOIN titles AS t 
ON t.emp_no = s.emp_no
AND t.to_date = '9999-01-01'
AND s.to_date = '9999-01-01'
GROUP BY title

　　

15.获取当前（to_date=’9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary：

1
2
3
4
5
6
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
2
3
4
SELECT emp_no, MAX(salary) 
FROM salaries 
WHERE salary NOT IN (SELECT MAX(salary) FROM salaries) 
AND to_date='9999-01-01'

或

1
2
3
4
SELECT emp_no, MAX(salary) 
FROM salaries 
WHERE salary < (SELECT MAX(salary) FROM salaries) 
AND to_date='9999-01-01'

方式二：（但是这种方式不能解决多个人工资相同的问题）

1
2
3
4
SELECT emp_no,salary 
FROM salaries s 
WHERE s.to_date = '9999-01-01'
ORDER BY s.salary DESC LIMIT 1,1

改进：
（子查询里面也需要限制to_date=’9999-01-01’，不然得到的不是当前第二多的工资）

1
2
3
SELECT emp_no, salary FROM salaries WHERE to_date = '9999-01-01' 
AND salary = (SELECT DISTINCT salary FROM salaries 
WHERE to_date = '9999-01-01' ORDER BY salary DESC LIMIT 1,1)

避免了2个问题：
1.首先这样可以解决多个人工资相同的问题；
2.另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资。

为什么要加distinct？limit不是只限制只拿一条吗？
　　——先执行distinct 后执行limit。

另：

1.“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where条件语句中不能使用聚合函数。
2.“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having条件语句中可以使用聚合函数。

SQL中提供的聚合函数可以用来统计、求和、求最值等等。
分类：

  • –COUNT：统计行数量
  • –SUM：获取单个列的合计值
  • –AVG：计算某个列的平均值
  • –MAX：计算列的最大值
  • –MIN：计算列的最小值

　　

16.查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by：

1
2
3
4
5
6
7
8
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`))；
1
2
3
4
5
6
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`));

答：
方式一：（注意这里只限定“当前薪水”即可，不用再加 AND e. to_date = ‘9999-01-01’）

1
2
3
4
5
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM salaries s, employees e 
WHERE s.emp_no = e.emp_no 
AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND s.to_date = '9999-01-01';

方式二：（使用内连接）

1
2
3
4
5
6
SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name
FROM salaries s 
INNER JOIN employees e 
ON s.emp_no = e.emp_no 
AND salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND s.to_date = '9999-01-01';

　　

17.查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工：

1
2
3
4
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
1
2
3
4
5
6
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
1
2
3
4
5
6
7
8
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`));

答：

1
2
3
4
SELECT e.last_name, e.first_name, d.dept_name 
FROM employees as e 
LEFT JOIN dept_emp AS de ON e.emp_no = de.emp_no
LEFT JOIN departments AS d ON de.dept_no = d.dept_no

解析：
本题思路为运用两次LEFT JOIN连接嵌套:

1. 第一次LEFT JOIN连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工；

2. 第二次LEFT JOIN连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工。

　　

18.查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth：

1
2
3
4
5
6
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.先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录
2.再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅，最后用别名growth代替

答：

1
2
3
4
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

　　本题的另一种解法也能通过测试，但实际上不严谨，只有在员工最后一条工资记录为最大值时成立，如果最后一次的工资调整为降薪，则此思路通不过。具体思路如下：

直接找到emp_no=10001的员工的工资记录，将其最大工资减去最小工资得到涨幅

1
2
SELECT (MAX(salary)-MIN(salary)) AS growth 
FROM salaries WHERE emp_no = '10001'

　　

19.查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序：

1
2
3
4
5
6
7
8
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`));
1
2
3
4
5
6
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`));

答：
方式一：（全部使用from并列查询）

1
2
3
4
5
6
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM 
(SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = '9999-01-01') AS sCurrent,
(SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date) AS sStart
WHERE sCurrent.emp_no = sStart.emp_no
ORDER BY growth;

方式二：（全部使用内连接inner join）

1
2
3
4
5
6
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM 
(SELECT s.emp_no, s.salary FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01') AS sCurrent
INNER JOIN (SELECT s.emp_no, s.salary FROM employees e INNER JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no
ORDER BY growth;

解析：

连接employees与salaries，建立两张表。
先得到一个员工当前工资表sCurrent：
（SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.to_date = ‘9999-01-01’）;

和一个员工入职时的工资表sStart：
（SELECT s.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND s.from_date = e.hire_date）;

然后连接这两个表，
最后限定在同一员工下（sCurrent.emp_no = sStart.emp_no）用当前工资减去入职工资（sCurrent.salary - sStart.salary）计算得到入职以来的薪水涨幅growth，并排序ORDER BY growth (默认升序ASC)。

　　

20.统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum：

1
2
3
4
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
1
2
3
4
5
6
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
1
2
3
4
5
6
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
2
3
4
5
SELECT d.dept_no, d.dept_name, COUNT(s.salary) AS sum 
FROM departments d, dept_emp de, salaries s 
WHERE d.dept_no = de.dept_no 
AND de.emp_no = s.emp_no 
GROUP BY d.dept_no;

方式二：

1
2
3
4
5
SELECT d.dept_no, d.dept_name, COUNT(s.salary) AS sum 
FROM departments d
INNER JOIN dept_emp de ON d.dept_no = de.dept_no 
INNER JOIN salaries s ON de.emp_no = s.emp_no
GROUP BY d.dept_no;

　　

21.对所有员工的当前(to_date=’9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列：

1
2
3
4
5
6
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`));

本题的主要思想是复用salaries表进行比较排名，具体思路如下：

1.从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
2.本题的精髓在于s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
3.千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()。
4.最后先以s1.salary逆序排列，再以 s1.emp_no顺序排列输出结果。

答：

1
2
3
4
5
6
7
SELECT s1.emp_no, s1.salary, COUNT(DISTINCT s2.salary) AS rank
FROM salaries AS s1, salaries AS s2
WHERE s1.salary <= s2.salary 
AND s1.to_date = '9999-01-01' 
AND s2.to_date = '9999-01-01' 
GROUP BY s1.emp_no 
ORDER BY s1.salary DESC, s1.emp_no ASC;

　　最后在支持ROW_NUMBER、RANK、DENSE_RANK等函数的SQL Server数据库中，有以下参考代码，可惜在本题的SQLite数据库中不支持。

1
2
SELECT emp_no, salaries, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
WHERE to_date = '9999-01-01' ORDER BY salary DESC, emp_no ASC

---------------- The End ----------------
0%