刷牛客SQL Part3

题目13

https://www.nowcoder.com/practice/72ca694734294dc78f513e147da7821e?tpId=82&tqId=29765&tPage=1&rp=&ru=/ta/sql&qru=/ta/sql/question-ranking
题目描述
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

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：*/
SELECT title, COUNT(title) AS t 
FROM titles
GROUP BY title 
HAVING t >= 2
/* 解法2：这里的count对任意一个属性计数都可以，改成to_date也能通过，其他属性都可以*/
select title, count(emp_no) as t
from titles
group by title
having t >= 2

一点笔记：
1、通过Group By分组
2、通过Having控制分组条件。having在group后。

题目14：

https://www.nowcoder.com/practice/c59b452f420c47f48d9c86d69efdff20?tpId=82&tqId=29766&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。（其实是去重）

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：参考上题，对emp_no也可以通过，但是本题多一个去重*/
SELECT title, COUNT(DISTINCT emp_no) AS t 
FROM titles
GROUP BY title 
HAVING t >= 2
/* 解法2：这里子查询的emp_no和title不能颠倒*/
select title , count(title) as t 
from (select distinct emp_no,title from titles)
group by title 
having t >= 2;

题目15：

https://www.nowcoder.com/practice/a32669eb1d1740e785f105fa22741d5c?tpId=82&tqId=29767&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

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：其实最好用<>来表示不等于*/
select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc
/* 解法2：*/
select  emp_no, birth_date, first_name, last_name, gender, hire_date 
from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc

题目16：

https://www.nowcoder.com/practice/c8652e9e5a354b879e2a244200f1eaae?tpId=82&tqId=29768&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

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

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：*/
SELECT t.title,avg(s.salary) AS avg
FROM salaries s ,titles t
where s.emp_no = t.emp_no
AND s.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
GROUP BY title
/* 解法2：不使用where，用join将表联结，则配合使用on*/
SELECT t.title,avg(s.salary)
FROM salaries as s INNER JOIN titles as t
ON s.emp_no = t.emp_no
AND s.to_date = '9999-01-01'
AND t.to_date = '9999-01-01'
GROUP BY title

一点笔记：where 是建表前先筛选，having是建表后再判断

题目17：

https://www.nowcoder.com/practice/8d2c290cc4e24403b98ca82ce45d04db?tpId=82&tqId=29769&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

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：在不考虑有薪水相同的员工的情况下。降序排序，跳过1个，取1个*/
select emp_no,salary
from salaries
where to_date = '9999-01-01'
order by salary desc limit 1,1 
/* 解法2：在考虑有薪水相同的员工的情况下，选择薪水倒数*/
SELECT emp_no, salary
FROM salaries
WHERE salary = (SELECT distinct salary
                FROM salaries
                WHERE to_date = '9999-01-01'
                GROUP BY salary
                ORDER BY salary DESC LIMIT 1,1 )  
/* 解法3：考虑方式和解法2一样，写法不同*/
select emp_no, salary 
from salaries
where to_date = '9999-01-01' 
and salary = (select distinct salary from salaries order by salary desc limit 1,1)

一点总结：distinct必须放在开头
select id, distinct name from A; --会提示错误，因为distinct必须放在开头

题目18：

https://www.nowcoder.com/practice/c1472daba75d4635b7f8540b837cc719?tpId=82&tqId=29770&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

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

题解：

/* 解法1：*/
SELECT e.emp_no, s1.salary, e.last_name, e.first_name
FROM employees e, salaries s1, salaries s2
WHERE e.emp_no = s1.emp_no
    AND s1.to_date = '9999-01-01'
    AND s2.to_date = '9999-01-01'
    AND s1.salary <= s2.salary
GROUP BY s1.emp_no
HAVING COUNT(DISTINCT s2.salary)=2
/* 解法2：*/
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e join salaries s 
on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = (
     select s1.salary
     from salaries s1 join salaries s2 
     on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' 
     and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )

一点总结：Part2的最后一题也是有难度，为什么每次都是最后一题难，唉。ORDER BY 语句用于根据指定的列对结果集进行排序。本题不让用排序。可以看到，这里将salaries表使用了两次，为s1和s2。而薪水第二多，意思就是只有一个人比他多，这样只要用salary表内连接，再分组，组内条数等于1的那条记录，就正好是第二多。如果要找第三多，那就让having count(*)=2就行了，只有两个人比那个人多。（假如说s1 和s2是 100 98 98 96 则 s1.100关联s2.100 s1.98关联s2.100 s2.98 s2.98 ，那么98对应的记录就是3条，去重后就是2条，而2条就以为着98第2大，只比100小）