牛客网SQL实战

select * from employees
where hire_date = (
select hire_date from employees 
    order by hire_date desc limit 2,1    
)

LIMIT m,n : 表示从第m+1条开始，取n条数据；

LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。

 

（1）首先需要加distinct去重。

假设 5-23（入职最晚日期）入职的有a,b,c 3人；

        5-22（入职第二晚日期）入职的有d,e 2人；

        5-21(入职倒数第三晚)入职的有f,g,h 3人；

        5-21前入职的若干...

若 不加distinct去重，那么按照日期倒序，limit 2,1（从倒数第2行开始，取一条数据）的查询结果为 5-23

加了distinct去重，会按入职日期进行分组，多个相同入职日期会分为一组，这样limit 2,1的结果即为 5-21。

（2）外层的where条件中根据子查询查出的倒数第三晚入职的日期，就能查询出符合条件的员工信息。

————————————————————————————————————————————————

select salaries.*, dept_manager.dept_no from salaries, dept_manager
where dept_manager.emp_no = salaries.emp_no 
and dept_manager.to_date ='9999-01-01' 
and salaries.to_date ='9999-01-01'

————————————————————————————————————————————————

select employees.last_name, employees.first_name, dept_emp.dept_no
from dept_emp, employees
where dept_emp.emp_no = employees.emp_no

select e.last_name, e.first_name, d.dept_no
from dept_emp as d, employees as e
where d.emp_no = e.emp_no

————————————————————————————————————————————————

SELECT ep.last_name, ep.first_name, dp.dept_no 
FROM employees ep 
LEFT JOIN dept_emp dp
ON ep.emp_no = dp.emp_no

注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

注意on与where有什么区别，两个表连接时用on，在使用left  jion时，on和where条件的区别如下：

1、  on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left  join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

————————————————————————————————————————————————

select employees.emp_no, salaries.salary 
from salaries, employees
where salaries.emp_no=employees.emp_no
and employees.hire_date = salaries.from_date
order by employees.emp_no DESC

————————————————————————————————————————————————

此题应注意以下四点：

1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数

2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t

3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件

4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可

/**  注意： 严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负 **/

select emp_no, COUNT(emp_no) as t
from salaries
GROUP by emp_no
HAVING COUNT(emp_no)>15

————————————————————————————————————————————————

也可以用distinct，但是大表一般用distinct效率不高，大数据量的时候都禁止用distinct，建议用group by解决重复问题。 

select salary from salaries
where to_date='9999-01-01'
group by salary
order by salary DESC

————————————————————————————————————————————————

select d.dept_no, d.emp_no, s.salary
from dept_manager d
Join salaries s
ON d.to_date='9999-01-01'
and s.to_date='9999-01-01'
and d.emp_no=s.emp_no

————————————————————————————————————————————————

select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where d.emp_no is null

 

————————————————————————————————————————————————

SELECT de.emp_no,dm.emp_no AS manager_no
FROM dept_manager AS dm,dept_emp AS de
WHERE de.emp_no != dm.emp_no 
AND de.dept_no = dm.dept_no
AND dm.to_date='9999-01-01'

————————————————————————————————————————————————

此题思路如下：

1、先用INNER JOIN连接两张表，限制条件是两 张表的emp_no相同，即d.emp_no = s.emp_no；

2、选取每个员工当前的工资水平，用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；

3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；

4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

1 2 3 4 5

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no  WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no

------------------------------------------分割线：若存在多条最大记录----------------------------------------

有同学提出疑问，如果存在多条最大记录怎么办？而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录，其余记录均被忽略。此时解法如下：

1、创建两张表，一张为maxsalary，用于存放当前每个部门薪水的最大值；另一张为currentsalary，用于存放当前每个部门所有员工的编号和薪水；

2、限定条件为两张表的 dept_no 和 salary 相等，这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了；

3、最后记得根据 currentsalary.dept_no 升序排列，输出与参考答案相同的记录表。

4、以下代码虽然很长，仔细一看都是基于上面的基础解法变化而来的，中心思想就是绕开 MAX 的特性限制，运用比较的方法选出多个相同的最大值。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary FROM  //创建maxsalary表用于存放当前每个部门薪水的最大值 (SELECT d.dept_no, MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no  WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no) AS maxsalary,  //创建currentsalary表用于存放当前每个部门所有员工的编号和薪水 (SELECT d.dept_no, s.emp_no, s.salary  FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no  WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' ) AS currentsalary //限定条件为两表的dept_no和salary均相等 WHERE currentsalary.dept_no = maxsalary.dept_no AND currentsalary.salary = maxsalary.salary //最后以currentsalary.dept_no排序输出符合要求的记录表 ORDER BY currentsalary.dept_no

————————————————————————————————————————————————

//先找出emp_no唯一的表格，再在其中查找 

select title, COUNT(title) as t
from 
(
    select distinct emp_no,title,from_date,to_date
    from titles
)
group by title
Having COUNT(title)>=2

还有其他解法

1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(DISTINCT emp_no)的值转换为t

3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

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

————————————————————————————————————————————————

select * from employees
where emp_no % 2==1
and last_name!='Mary'
ORDER BY hire_date DESC

————————————————————————————————————————————————

select title, avg(salary) as avg
from titles t INNER Join salaries s
On t.emp_no=s.emp_no
where t.to_date='9999-01-01'
and s.to_date='9999-01-01'
group by t.title

 还有其他解法

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

select title,avg(salary) as avg from salaries,titles where salaries.emp_no = titles.emp_no and salaries.to_date='9999-01-01'
and titles.to_date='9999-01-01'
group by title ;正确

select title,avg(salary) as avg from salaries,titles where salaries.to_date='9999-01-01'
and titles.to_date='9999-01-01'
group by title  having salaries.emp_no = titles.emp_no  ;不正确,因为group by之后的表中只有 title，avg字段可以继续进行，having无法继续处理。其实可以理解为title为唯一ID,其他列就是一堆数，你只能对他进行统计呗。我是这样理解的，可能有不足之处。

————————————————————————————————————————————————

select emp_no, salary
from salaries
where salaries.to_date='9999-01-01'
ORDER by salary DESC limit 1,1

 上面的代码其实并不完整，你需要改进一下（哪里不完整，比如说并列第一的，显然就需要一个消除多余salary的方法）

select emp_no, salary
from salaries
where salaries.to_date='9999-01-01'
and salary = 
(
    select distinct salary from salaries
    order by salary DESC limit 1,1
)

避免了2个问题：

(1) 首先这样可以解决多个人工资相同的问题；

(2) 另外，筛选出第二多的工资时要注意distinct salary，否则不能选出第二多的工资。

————————————————————————————————————————————————

本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合

1、先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01'

2、再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01'与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者 

select E.emp_no, Max(S.salary) as salary, E.last_name, E.first_name
from employees E
inner join salaries S
ON S.emp_no=E.emp_no
where to_date='9999-01-01'
and S.salary Not in 
(
    select Max(salary) from salaries where to_date = '9999-01-01'
)

————————————————————————————————————————————————

SELECT e.last_name, e.first_name, dp.dept_name FROM employees AS e
LEFT JOIN dept_emp AS d ON e.emp_no = d.emp_no
LEFT JOIN departments AS dp ON d.dept_no = dp.dept_no;

为什么要用left join，而不是inner join？是因为inner join会删除空的一行，对于该题由于那些没有安排岗位的人，dept_name必然是null，所以只能用left join。 

————————————————————————————————————————————————

本题严谨的思路如下：

1、先分别找到emp_no=10001的员工的第一次工资记录与最后一次工资记录

2、再将最后一次工资记录减去第一次工资记录得到入职以来salary的涨幅，最后用别名growth代替

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

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

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

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

————————————————————————————————————————————————

本题思路是先分别用两次LEFT JOIN左连接employees与salaries，建立两张表，分别存放员工当前工资（sCurrent）与员工入职时的工资（sStart），再用INNER JOIN连接sCurrent与sStart，最后限定在同一员工下用当前工资减去入职工资。

方法一：内层用LEFT JOIN，外层用INNER JOIN（内层也可以改用 INNER JOIN）

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth FROM (SELECT s.emp_no, s.salary FROM employees e LEFT 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 LEFT 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

方法二：内外都层用FROM并列查询

1 2 3 4 5

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

————————————————————————————————————————————————

select d.dept_no ,dept.dept_name, count(salary) 
from salaries s,dept_emp d,departments dept
where s.emp_no = d.emp_no 
and d.dept_no=dept.dept_no 
group by dept.dept_no

————————————————————————————————————————————————

————————————————————————————————————————————————

 

————————————————————————————————————————————————

SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no FROM salaries s INNER JOIN dept_emp de
ON s.emp_no = de.emp_no AND s.to_date = '9999-01-01' ) AS sem,
(SELECT s.salary, s.emp_no, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
ON s.emp_no = dm.emp_no AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

————————————————————————————————————————————————

SELECT de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
from dept_emp de INNER JOIN titles t
ON t.emp_no = de.emp_no AND de.to_date = '9999-01-01' AND t.to_date = '9999-01-01'

INNER JOIN departments dp
ON dp.dept_no = de.dept_no 

group by de.dept_no, t.title

————————————————————————————————————————————————

这种涉及到列中变量求差之类的，最好从同一张表中，定义两个表变量

select s1.emp_no, s2.from_date, (s2.salary-s1.salary) as salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no
and s2.salary - s1.salary > 5000
AND strftime('%Y',s2.to_date) - strftime('%Y',s1.to_date) = 1
ORDER BY salary_growth DESC;

————————————————————————————————————————————————

SELECT last_name||" "||first_name AS Name FROM employees 

————————————————————————————————————————————————

create table actor
(
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')) -- ,
-- PRIMARY KEY(actor_id)
)

————————————————————————————————————————————————

INSERT INTO actor
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33')

————————————————————————————————————————————————

insert IGNORE into actor
values(3,'ED','CHASE','2006-02-15 12:34:33');

————————————————————————————————————————————————

 

create table actor_name
(
    first_name varchar(45) not null,
    last_name varchar(45) not null
);
insert into actor_name select first_name,last_name from actor

————————————————————————————————————————————————

create unique index uniq_idx_firstname on actor(first_name);
create index idx_lastname on actor(last_name);

 先用 CREATE UNIQUE INDEX ... ON ... 对first_name创建唯一索引值，再用 CREATE INDEX ... ON ... 对last_name创建普通索引值

PS:create unique index uniq_idx_firstname 
on actor(first_name);
create index idx_lastname 
on actor(last_name);

这么写是不对的，不能换行

————————————————————————————————————————————————

create view actor_name_view (first_name_v,last_name_v) as
select first_name,last_name from actor

————————————————————————————————————————————————

alter table actor add column create_date datetime not null default '0000-00-00 00:00:00';

————————————————————————————————————————————————

update titles_test set to_date=null,from_date='2001-01-01' where to_date='9999-01-01';

————————————————————————————————————————————————

delete from titles_test
where id != 
(
    select min(id) from titles_test
)

————————————————————————————————————————————————

replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');

 

————————————————————————————————————————————————

alter table titles_test RENAME TO titles_2017 

————————————————————————————————————————————————

update salaries set salary=salary*1.1
where emp_no in (
    select emp_no from emp_bonus
)

————————————————————————————————————————————————

————————————————————————————————————————————————

select dept_no, group_concat(emp_no) AS employees
FROM dept_emp GROUP BY dept_no

————————————————————————————————————————————————

select avg(salary) as avg_salary from salaries
where to_date = '9999-01-01'
and salary not in (select min(salary) from salaries)
and salary not in (select max(salary) from salaries)

————————————————————————————————————————————————

select e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, 
(case eb.btype
when 1 then s.salary*0.1
when 2 then s.salary*0.2
else s.salary*0.3 end) as bonus
from employees e
Inner join emp_bonus eb on e.emp_no=eb.emp_no
Inner join salaries s on s.emp_no=e.emp_no
where s.to_date='9999-01-01'

————————————————————————————————————————————————

 

————————————————————————————————————————————————

 

————————————————————————————————————————————————

 

————————————————————————————————————————————————

 

————————————————————————————————————————————————

 

————————————————————————————————————————————————