sql语句练习

1.求第二高的薪水，如果不存在第二高的薪水，那么查询应返回 null。

CREATE TABLE IF NOT EXISTS Employee (Id INT, Salary INT);
TRUNCATE TABLE Employee;
INSERT INTO Employee (Id, Salary) VALUES ('1', '100');
INSERT INTO Employee (Id, Salary) VALUES ('2', '200');
INSERT INTO Employee (Id, Salary) VALUES ('3', '300');

题解：

方法一：使用子查询和 LIMIT 子句

将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资，offset前是查询结果的个数，offset后是跳过前几条查询的语句

然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;

方法二：使用 IFNULL 和 LIMIT 子句
解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数，如下所示
 

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

2.过去30天的用户活动

CREATE TABLE IF NOT EXISTS Activity (user_id INT, session_id INT, activity_date DATE, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'));
TRUNCATE TABLE Activity;
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('1', '1', '2019-07-20', 'open_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('1', '1', '2019-07-20', 'scroll_down');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('1', '1', '2019-07-20', 'end_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('2', '4', '2019-07-20', 'open_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('2', '4', '2019-07-21', 'send_message');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('2', '4', '2019-07-21', 'end_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('3', '2', '2019-07-21', 'open_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('3', '2', '2019-07-21', 'send_message');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('3', '2', '2019-07-21', 'end_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('3', '5', '2019-07-21', 'open_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('3', '5', '2019-07-21', 'scroll_down');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('3', '5', '2019-07-21', 'end_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('4', '3', '2019-06-25', 'open_session');
INSERT INTO Activity (user_id, session_id, activity_date, activity_type) VALUES ('4', '3', '2019-06-25', 'end_session');

该表没有主键，它可能有重复的行
activity_type 列是 ENUM（“ open_session”，“ end_session”，“ scroll_down”，“ send_message”）中的某一类型

请注意，每个会话完全属于一个用户
编写SQL查询以查找截至2019年7月27日（含）的30天内每个用户的平均会话数，四舍五入到小数点后两位。我们只统计那些会话期间用户至少进行一项活动的有效会话。

题解：

方法一：COUNT 和 DATEDIFF
思路

本题的重点就是要理解每个用户的平均会话数。用户即为 user_id，无论什么时候永远不会变。会话是对应的 session_id，用户的 session_id 会在特定的情况下改变，比如 end_session 后再 open_session。所以我们只需要统计总的会话数和总的用户数，相除就是平均数，即：
COUNT(session_id) / COUNT(user_id)

这个数字还需要加工处理：

由于表里面可能有重复的数据，所以需要使用 DISTINCT 去重。
使用 ROUND() 保留两位有效数字。
使用 IFNULL 处理返回结果为 null 的情况。
只需要查找截至 2019-07-27 日（含）的 30 天内的数据，有两种办法（注意是截至不是截止）：
计算出第一天，使用 BETWEEN ：WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'。
使用 datediff() 函数，计算当天与最后一天的差值：WHERE datediff('2019-07-27',activity_date) < 30。

SELECT IFNULL(ROUND(COUNT(DISTINCT session_id) / COUNT(DISTINCT user_id), 2), 0) AS average_sessions_per_user
FROM Activity
WHERE DATEDIFF('2019-07-27', activity_date) < 30
-- WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'