原创 SQL实战-数据分析师-笔试面试-每日一练-坚持

1、case 判定条件

题目描述：

获取有奖金的员工相关信息。

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 `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`));
create table emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null);
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`));

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

代码实现：

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 e.emp_no=s.emp_no 
where s.to_date='9999-01-01'

注意：这里主要考察了cse then判定条件的使用。同时需要注意三个表之间内连接，同时需要注意不要忘记选择条件时间为9999-01-01

case的详细使用：https://www.cnblogs.com/HDK2016/p/8134802.html

2、求和

按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。 具体结果如下Demo展示。。

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

输出格式:

代码实现：

select s1.emp_no,s1.salary,
(select sum(s2.salary) from salaries s2 
where s2.emp_no<=s1.emp_no and s2.to_date='9999-01-01') 
as running_total 
from salaries s1
 where s1.to_date='9999-01-01'
order by s1.emp_no ASC

解题思路：
题目的巧妙指出就是找出工资累加和，这里需要借助一个表格，巧妙的使用了s1.emp_no<=s2.emp_no

3、输出排名为奇数

题目描述：

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

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

如，输入为：

INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

输出格式:

因为Georgi按first_name排名为3，Anneke按first_name排名为1，所以会输出这2个，且输出时不需排序。

代码实现：

SELECT e1.first_name FROM
employees e1
WHERE
(SELECT count(*) FROM employees e2
WHERE e1.first_name >=e2.first_name)%2=1

4、简单查询

题目描述：

在牛客刷题的小伙伴们都有着牛客积分，积分(grade)表简化可以如下:

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

代码实现：

select number 
from grade
group by number having count(id)>=3

5、排序并输出序号

有一个通过题目个数的(passing_number)表，id是主键，简化如下:

第1行表示id为1的用户通过了4个题目;

…

第6行表示id为6的用户通过了4个题目;

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

id为5的用户通过了5个排名第1，

id为1和id为6的都通过了2个，并列第2

代码实现：

select id,number, dense_rank() over
(order by number DESC )as rank
from passing_number
order by number DESC,id ASC