牛客sql第1-20题

SQL1

//第一种：适用于最晚的员工如果不止一个的情况
//先找到最晚的hire_data，再用条件查到
SELECT * FROM employees 
    WHERE hire_date =(SELECT MAX(hire_date) FROM employees)
//第二种：不适用于最晚的员工如果不止一个的情况。
//降序排序，并且只显示第一行  
//limit(x,y)代表第x行开始共显示y行  limit x [offset y]代表取前x行数据，但是跳过y行
SELECT * FROM employees 
    ORDER BY hire_date DESC LIMIT 0,1

SQL2

//适用于相同时间员工不止一个的情况
//方法同SQL1
//注意DISTINCT去重
SELECT * FROM employees
    WHERE hire_date = (SELECT DISTINCT hire_date FROM employees 
    ORDER BY hire_date DESC LIMIT 2,1)    
//不适用于相同时间员工不止一个的情况
//方法同SQL1
SELECT * FROM employees 
    ORDER BY hire_date DESC LIMIT 1 OFFSET 2

SQL3

//给表起别名 FROM tablea t 把tablea起名为t 
//等值连接 where连接两个表
SELECT s.* ,d.dept_no FROM salaries s,dept_manager d
  WHERE s.emp_no = d.emp_no
  ORDER BY s.emp_no

SQL4

//第一种：等值连接
SELECT e.last_name,e.first_name,d.dept_no 
  FROM employees e,dept_emp d
  WHERE e.emp_no = d.emp_no
//第二种：自然连接 NATURAL JOIN
 SELECT e.last_name,e.first_name,d.dept_no 
  FROM employees e
  NATURAL JOIN dept_emp d

SQL5

/* INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。*/
//内外连接需要用ON做条件
SELECT e.last_name,e.first_name,d.dept_no 
  FROM employees e LEFT JOIN dept_emp d
  ON e.emp_no = d.emp_no

SQL7

//GROUP BY 可以按照属性对表分组 
//当用上 GROUP BY后的聚集函数是对每一个组进行聚集
//HAVING和 WHERE类似，都是起到过滤作用，但是HAVING是对分组进行过滤，WHERE是对行进行过滤
SELECT emp_no,COUNT(emp_no) FROM salaries
GROUP BY emp_no HAVING COUNT(emp_no) > 15

SQL8

//同SQL2
SELECT DISTINCT salary FROM salaries
  ORDER BY salary DESC

SQL10

//不包含用 NOT IN(...)
SELECT emp_no FROM employees
  WHERE emp_no NOT IN(
    SELECT emp_no FROM dept_manager
  )

SQL11

//等值连接+条件判断
SELECT emp.emp_no,man.emp_no FROM dept_emp emp,dept_manager man
  WHERE emp.dept_no = man.dept_no && emp.emp_no != man.emp_no

SQL12

//首先是内连接，若不匹配直接不显示。因为两个表有两个相同属性，from_date数据不同所以不能用自然连接
//然后用窗口函数 OVER(PARITION BY... ORDER BY) as ... 可以分组进行排序，并且不修改原来的数据只添加一个排序列
//要求TOPN的题都可以用窗口函数嵌套上查询和where条件把rank=N的取出来
SELECT t.dept_no,t.emp_no,t.salary
  FROM (SELECT d.dept_no,d.emp_no,d.salary,DENSE_RANK() 
          OVER (PARTITION BY d.dept_no ORDER BY d.salary DESC) AS sal_rank
          FROM (SELECT emp.dept_no,emp.emp_no,s.salary
                  FROM dept_emp emp 
                  INNER JOIN salaries s ON emp.emp_no = s.emp_no) d
       ) t
  WHERE t.sal_rank = 1

SQL15

//值不相等用!=，非空用 IS NOT NULL
//字符串用''引上
//多个条件用 AND连接
SELECT * FROM employees
  WHERE emp_no % 2 != 0 AND last_name != 'Mary'
  ORDER BY hire_date DESC

SQL16

//要对分组后的聚集函数的结果进行 ORDER BY，需要先对结果起个别名
SELECT t.title,AVG(s.salary) AS sal_avg FROM titles t
  INNER JOIN salaries s ON t.emp_no = s.emp_no
  GROUP BY t.title 
  ORDER BY sal_avg

SQL17

//同SQL1
SELECT emp_no,salary FROM salaries
  ORDER BY salary DESC
  LIMIT 1,1

SQL18

//怪题
SELECT s.emp_no, s.salary, e.last_name, e.first_name
FROM employees e NATURAL JOIN salaries s
WHERE s.salary = (
  select max(salary)
  from salaries
  where salary != ( select max(salary) from salaries )
)

SQL19

//双左连接，左边有数据右边没有数据也显示
SELECT e.last_name,e.first_name,d.dept_name
FROM employees e 
LEFT JOIN dept_emp t ON e.emp_no = t.emp_no
LEFT JOIN departments d ON t.dept_no = d.dept_no