MySQL实例练习---day06

SQL 59

• 给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
  bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。
  当前薪水表示to_date=‘9999-01-01’

分析：本题主要考察的是 MySQL 中 case when 的基本用法

case <单值表达式>
	when <表达式值> then <返回值>
	when <表达式值> then <返回值>
	...
	else <返回值>
end

SELECT t1.emp_no, e.first_name, e.last_name, t1.btype, t1.salary,(
CASE t1.btype 
WHEN 1 THEN t1.salary * 0.1
WHEN 2 THEN t1.salary * 0.2
WHEN 3 THEN t1.salary * 0.3
END
) AS bonus
FROM employees e
JOIN 
(SELECT b.emp_no,b.btype, s.salary FROM salaries s
JOIN emp_bonus b
ON b.emp_no = s.emp_no AND s.to_date='9999-01-01') t1
ON e.emp_no = t1.emp_no

SQL 60

• 统计salary的累计和running_total，其中running_total为前N个当前( to_date =
  ‘9999-01-01’)员工的salary累计和

SELECT t1.emp_no, t1.salary, SUM(t2.salary) running_total FROM salaries t1
,(SELECT emp_no, salary FROM salaries WHERE to_date = '9999-01-01') t2
WHERE t1.emp_no >= t2.emp_no  AND t1.to_date = '9999-01-01' 
GROUP BY t1.emp_no, t1.salary

t1.emp_no >= t2.emp_no，SUM(t2.salary) running_total 结合起来就是查找小于等于 t1.emp_no 的 t2.salary 的总和

SQL 61

• 对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

mysql 排序四大函数：
1、row_number() ：依次排序，重复值序号不相同
2、rank()：叠加、跳动排序，重复值序号相同，且序号不连续
3、dense_rank()：叠加、依次排序，重复值序号相同，且序号连续
4、ntile()：排序分组
NTILE(group_num) 将所有记录分成 group_num 个组，每组序号相同

SELECT t.first_name FROM 
(SELECT first_name, ROW_NUMBER() OVER(ORDER BY first_name) rank_num FROM employees ORDER BY emp_no) t
WHERE t.rank_num % 2 = 1

SQL 62

• id为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分

分组统计：

SELECT number FROM grade 
GROUP BY number
HAVING COUNT(number) >= 3

SQL 63

• 请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列

SELECT id, number, DENSE_RANK() over(order by number desc ) AS t_rank
FROM passing_number
order by number desc,id

SQL 64

• 请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序

SELECT p.id, p.name, t.content FROM person p
LEFT JOIN task t
ON p.id = t.person_id
ORDRE BY p.id