1.按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees 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`)); 输出格式:
dept_noemployeesd00110001,10002d00210006d00310005d00410003,10004d00510007,10008,10010d00610009,10010考察点：group_concat
SQLite: SELECT dept_no, group_concat(emp_no) AS employees FROM dept_emp GROUP BY dept_no MySQL: select dept_no,group_concat(emp_no SEPARATOR ',') from dept_emp group by dept_no;2.分页查询employees表，每5行一页，返回第2页的数据 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`));
考察点：Limit
方法一：利用 LIMIT 和 OFFSET 关键字。LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回（第一条记录序号为0），也可理解为跳过多少条记录后开始返回。 SELECT * FROM employees LIMIT 5 OFFSET 5 方法二：只利用 LIMIT 关键字。注意：在 LIMIT X,Y 中，Y代表返回几条记录，X代表从第几条记录开始返回（第一条记录序号为0），切勿记反。 SELECT * FROM employees LIMIT 5,5
3.使用含有关键字exists查找未分配具体部门的员工的所有信息。
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`)); 输出格式:
emp_nobirth_datefirst_namelast_namegenderhire_date100111953-11-07MarySluisF1990-01-22考察点：IN和EXISTS
方法一： EXISTS SELECT * FROM employees WHERE NOT EXISTS (SELECT emp_no FROM dept_emp WHERE employees.emp_no = dept_emp.emp_no); 方法二：IN SELECT * FROM employees WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp); 什么时候用EXISTS，什么时候用IN？ 主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下： 当主表比从表大时，IN查询的效率较高； 当从表比主表大时，EXISTS查询的效率较高； 原因如下： in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次 exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
4.获取有奖金的员工相关信息。 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' 输出格式:
emp_nofirst_namelast_namebtypesalarybonus10001GeorgiFacello1889588895.810002BezalelSimmel27252714505.410003PartoBamford34331112993.310004ChirstianKoblick1740577405.7考察点：CASE WHEN
SELECT e.emp_no, e.first_name, e.last_name, b.btype, s.salary, (CASE b.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 AS e INNER JOIN emp_bonus AS b ON e.emp_no = b.emp_no INNER JOIN salaries AS s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'5.按照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`)); 输出格式:
emp_nosalaryrunning_total100018895888958100027252716148510003433112047961000474057278853100059469237354510006433114168561000788070504926100099540960033510010944096947441001125828720572考察点：窗口函数
SELECT emp_no,salary, SUM(salary) OVER (ORDER BY emp_no) AS running_total FROM salaries WHERE to_date = '9999-01-01';6.对于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');
输出格式:
first_nameGeorgiAnneke
考察点：窗口函数
select first_name from ( select e2.first_name,( select count(*) from employees as e1 where e1.first_name <= e2.first_name ) as rownum from employees as e2 where rownum % 2 =1 ) 计算行号的方法 : 有多少个小于等于e2.first_name的记录的个数就是e2.first_name的行号
7.牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.有一个登录(login)记录表，简况如下:
请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:
考察点：窗口函数
常规思路： select u.name as u_n ,c.name as c_n ,max(date) as d from login l, user u, client c where l.user_id = u.id and l.client_id = c.id group by u.name /*开窗函数分组求最大时间，然后用子查询筛选*/ select n.un u_n, n.cn c_n,n.d from( select u.name un, c.name cn, l.date, (max(l.date) over(partition by l.user_id)) d from login l,user u,client c where l.user_id=u.id and c.id=l.client_id ) n where n.date=n.d order by u_n;
8.牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，有一个登录(login)记录表，简况如下:
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:
查询结果表明:
id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存
固次日成功的留存率为 2/4=0.5(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)
考察点：逻辑思维
（第一天登录的新用户并且第二天也登录的用户）/（总用户）即为新登录用户的次日成功的留存率 总用户其实挺好算，如下： select count(distinct user_id) from login 找到每个用户第一天登陆的日子，其实挺好找，和前面找最近登录的日子差不多，一个是max，一个是min: select user_id,min(date) from login group by user_id 比如上面查找语句是1，2020-10-12；那么如果找到一个结果为1，2020-10-13的那么是不是就符合结果了，于是可以如下写: select user_id,date(min(date),'+1 day') from login group by user_id 这样就可以找到所有的在第一天登录的新用户并且第二天也登录的用户，以及第二天的日期。 所以从这个里面找到所有的count(distinct user_id)除以总用户就可以得到结果了，于是整个sql语句如下: select round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3) from login where (user_id,date) in (select user_id,date(min(date),'+1 day') from login group by user_id);
9.牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，有一个登录(login)记录表，简况如下:
请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:(输出0，可以用sqlite的ifnull函数尝试实现，select ifnull(null,1)的输出是1)
考察点：窗口函数
思路是窗口函数获取登录的次数，最早就是首次登录，然后分组对rank=1的求和即可 select a.date, sum(case when rank=1 then 1 else 0 end) new from (select date, row_number() over(partition by user_id order by date) rank from login) a group by date;
10.牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。有一个登录(login)记录表，简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户 第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户 最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:
查询结果表明: 2020-10-12登录了3个(id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667; 2020-10-13没有新用户登录，输出0.000; 2020-10-14登录了1个(id为4)新用户，2020-10-15，id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000; 2020-10-15没有新用户登录，输出0.000;
(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)
考察点：逻辑思路
求日期的前一天或者后一天，sqlite是date('2020-10-01','+1 day') 和date('2020-10-01','-1 day')；mysql是date_add('2020-10-01', interval 1 day)和date_add('2020-10-01', interval -1 day)。 #方法1： union后面 不是很懂 SELECT a.date, ROUND(COUNT(b.user_id) * 1.0/COUNT(a.user_id), 3) AS p FROM ( SELECT user_id, MIN(date) AS date FROM login GROUP BY user_id) a LEFT JOIN login b ON a.user_id = b.user_id AND b.date = date(a.date, '+1 day') GROUP BY a.date UNION SELECT date, 0.000 AS p FROM login WHERE date NOT IN ( SELECT MIN(date) FROM login GROUP BY user_id) ORDER BY date; #方法2 select a.date ,round( case when count(distinct a.user_id) = 0 then 0 else count(distinct b.user_id)*1.0/count(distinct a.user_id ) end ,3) as p from ( -- 每一天对应的新用户user_id，没有新用户的用null表示 select t0.date as date ,t1.user_id as user_id from ( select date from login group by date ) as t0 left join ( -- 新用户表 select user_id ,min(date) as date from login group by user_id ) as t1 on t0.date = t1.date ) as a left join login as b on a.user_id = b.user_id and b.date = date(a.date,'+1 day') group by a.date;By Mary Super @ 2020.10.26
