leetcode数据库刷题一

一

题目
Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

        +----+-------+--------+--------------+
        | Id | Name  | Salary | DepartmentId |
        +----+-------+--------+--------------+
        | 1  | Joe   | 70000  | 1            |
        | 2  | Henry | 80000  | 2            |
        | 3  | Sam   | 60000  | 2            |
        | 4  | Max   | 90000  | 1            |
        +----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

       +----+----------+
       | Id | Name     |
       +----+----------+
       | 1  | IT       |
       | 2  | Sales    |
       +----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

     +------------+----------+--------+
     | Department | Employee | Salary |
     +------------+----------+--------+
     | IT         | Max      | 90000  |
     | Sales      | Henry    | 80000  |
     +------------+----------+--------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
我的答案

select Department.name Department,Employee.name Employee,max(Salary) as Salary
from Employee join  Department on Employee.DepartmentId=Department.id
group by Department.Id

存在问题
当一个部门，有两个人工资相同高时，不适用。

参考答案

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

二

题目
Employee表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

           +----+-------+--------+--------------+
           | Id | Name  | Salary | DepartmentId |
           +----+-------+--------+--------------+
           | 1  | Joe   | 70000  | 1            |
           | 2  | Henry | 80000  | 2            |
           | 3  | Sam   | 60000  | 2            |
           | 4  | Max   | 90000  | 1            |
           | 5  | Janet | 69000  | 1            |
           | 6  | Randy | 85000  | 1            |
           +----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

          +----+----------+
          | Id | Name     |
          +----+----------+
          | 1  | IT       |
          | 2  | Sales    |
          +----+----------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

         +------------+----------+--------+
         | Department | Employee | Salary |
         +------------+----------+--------+
         | IT         | Max      | 90000  |
         | IT         | Randy    | 85000  |
         | IT         | Joe      | 70000  |
         | Sales      | Henry    | 80000  |
         | Sales      | Sam      | 60000  |
         +------------+----------+--------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-top-three-salaries
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
参考答案：

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

反思
不要妄想一口吃成胖子，对于多表操作在脑子不够用的情况下可以将表分开思考，分别考虑每个表中需要的信息，然后通过嵌套查询或是连接查询的方法连接起来。

三.日期比较

题目

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。

        +---------+------------------+------------------+
        | Id(INT) | RecordDate(DATE) | Temperature(INT) |
        +---------+------------------+------------------+
        |       1 |       2015-01-01 |               10 |
        |       2 |       2015-01-02 |               25 |
        |       3 |       2015-01-03 |               20 |
        |       4 |       2015-01-04 |               30 |
       +---------+------------------+------------------+

例如，根据上述给定的 Weather 表格，返回如下 Id:

我的解答

错误

正确答案

SELECT
    weather.id AS Id
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1
        AND weather.Temperature > w.Temperature
;

反思

DATEDIFF日期比较
DATEDIFF('2007-12-31','2007-12-30');   # 1
DATEDIFF('2010-12-30','2010-12-31');   # -1

四.sum()函数，round()函数

题目

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

    +----+-----------+-----------+---------+--------------------+----------+
    | Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
    +----+-----------+-----------+---------+--------------------+----------+
    | 1  |     1     |    10     |    1    |     completed      |2013-10-01|
    | 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
    | 3  |     3     |    12     |    6    |     completed      |2013-10-01|
    | 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
    | 5  |     1     |    10     |    1    |     completed      |2013-10-02|
    | 6  |     2     |    11     |    6    |     completed      |2013-10-02|
    | 7  |     3     |    12     |    6    |     completed      |2013-10-02|
    | 8  |     2     |    12     |    12   |     completed      |2013-10-03|
    | 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
    | 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
   +----+-----------+-----------+---------+--------------------+----------+

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

       +----------+--------+--------+
       | Users_Id | Banned |  Role  |
      +----------+--------+--------+
      |    1     |   No   | client |
      |    2     |   Yes  | client |
      |    3     |   No   | client |
      |    4     |   No   | client |
      |    10    |   No   | driver |
      |    11    |   No   | driver |
      |    12    |   No   | driver |
      |    13    |   No   | driver |
      +----------+--------+--------+

写一段 SQL 语句查出 2013年10月1日至 2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

    +------------+-------------------+
    |     Day    | Cancellation Rate |
    +------------+-------------------+
    | 2013-10-01 |       0.33        |
    | 2013-10-02 |       0.00        |
    | 2013-10-03 |       0.50        |
    +------------+-------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/trips-and-users
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

代码

select tp.Request_at as Day , round(sum(tp.Status!='completed')/count(*),2) as 'Cancellation Rate' 
from Trips as tp left join Users as us on tp.Client_Id=us.Users_Id 
where (tp.Request_at between '2013-10-01' and '2013-10-03') and (us.Banned='No') 
group by tp.Request_at

反思

count(),统计符合条件的行，除了count(*)外会跳过空值的行
sum

1.sum() 函数用于计算某一字段中所有行的数值之和（ sum 求和时会对 null 进行过滤，不计算）
2.使用 sum(条件) 进行对符合条件的结果行数进行求和;

    select sum(user_id is null),sum(aid = 1) from user_auth;
    #如果没有符合条件的元组，返回null,不是0

五

题目

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、人流量 (people)。
请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

例如，表 stadium：

    +------+------------+-----------+
    | id   | visit_date | people    |
    +------+------------+-----------+
    | 1    | 2017-01-01 | 10        |
    | 2    | 2017-01-02 | 109       |
    | 3    | 2017-01-03 | 150       |
    | 4    | 2017-01-04 | 99        |
    | 5    | 2017-01-05 | 145       |
    | 6    | 2017-01-06 | 1455      |
    | 7    | 2017-01-07 | 199       |
    | 8    | 2017-01-08 | 188       |
    +------+------------+-----------+

对于上面的示例数据，输出为：

   +------+------------+-----------+
   | id   | visit_date | people    |
   +------+------------+-----------+
   | 5    | 2017-01-05 | 145       |
   | 6    | 2017-01-06 | 1455      |
   | 7    | 2017-01-07 | 199       |
   | 8    | 2017-01-08 | 188       |
   +------+------------+-----------+

提示：
每天只有一行记录，日期随着 id 的增加而增加。
来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/human-traffic-of-stadium
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

我的答案（微笑）

(select s1.id id ,s1.visit_date visit_date,s1.people people
from stadium s1 join stadium s2 on s1.id+1=s2.id join  stadium s3 on s1.id+2=s3.id
where s1.people>=100 and s2.people>=100 and s3.people>=100)

union

(select s3.id id ,s3.visit_date visit_date,s3.people people
from stadium s1 join stadium s2 on s1.id+1=s2.id join  stadium s3 on s1.id+2=s3.id
where s1.people>=100 and s2.people>=100 and s3.people>=100
)
union
(select s2.id id ,s2.visit_date visit_date,s2.people people
from stadium s1 join stadium s2 on s1.id+1=s2.id join  stadium s3 on s1.id+2=s3.id
where s1.people>=100 and s2.people>=100 and s3.people>=100)

order by id

其他人的答案

SELECT distinct a.*
FROM stadium as a,stadium as b,stadium as c
where ((a.id = b.id-1 and b.id+1 = c.id) or
       (a.id-1 = b.id and a.id+1 = c.id) or
       (a.id-1 = c.id and c.id-1 = b.id))
  and (a.people>=100 and b.people>=100 and c.people>=100)
order by a.id;

六

题目

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：

      +---------+---------+
      |    id   | student |
     +---------+---------+
     |    1    | Abbot   |
     |    2    | Doris   |
     |    3    | Emerson |
     |    4    | Green   |
     |    5    | Jeames  |
    +---------+---------+

假如数据输入的是上表，则输出结果如下：

    +---------+---------+
    |    id   | student |
    +---------+---------+
    |    1    | Doris   |
    |    2    | Abbot   |
    |    3    | Green   |
    |    4    | Emerson |
    |    5    | Jeames  |
    +---------+---------+

注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。
来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/exchange-seats
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

答案

FROM seat a LEFT JOIN seat b ON if(a.id&1,a.id=b.id-1,a.id=b.id+1) 
ORDER BY id;

反思

七

题目

好评率是会员对平台评价的重要指标。现在需要统计2018年1月1日到2018年1月31日，用户'小明'提交的母婴类目"花王"品牌的好评率（好评率=“好评”评价量/总评价量）:
用户评价详情表：a
字段：id（评价id，主键），
create_time（评价创建时间，格式'2017-01-01'），
user_name(用户名称)，
goods_id(商品id，外键) ，
sub_time（评价提交时间，格式'2017-01-01 23:10:32'），
sat_name（好评率类型，包含：“好评”、“中评”、“差评”）
商品详情表：b
字段：good_id（商品id，主键），
bu_name（商品类目）,
brand_name(品牌名称)

答案

slelect sum(CASE  WHEN sat_time = '好评' THEN 1 ELSE 0 END)/count(sat_time) as good_rate
from a join b on a.goods_id=b.good_id
where a.user_name="小明" and brand_name="花王" and bu_name="母婴" and sub_time between 2018-01-01 and 2018-01-31

反思：

SELECT SUM(if(category=1,size,0)) ,COUNT(if(category=1,true,null)) FORM t_file;

解析：

sum（if(category=1,size,0))中 sum函数返回一个值类型的数值，如果category=1,则返回size,如果category不等于1就返回0。
count(if(category=1,true,null))中count函数返回一个布尔值类型的数值，如果category=1,返回true,如果category不等于1返回null，如果写成count(If(category=1,1,0) 则返回的全是true,也就是说全都会计数，而count()间断内容是true还是null,如果不是null就计数，如果是null就不计数。

所以count(if())的写法应该是count(if(表达式表达式，true,null));

八

题目

Table: Books

 +----------------+---------+
 | Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id is the primary key of this table.

Table: Orders

 +----------------+---------+
 | Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+

order_id is the primary key of this table.
book_id is a foreign key to the Books table.

Write an SQL query that reports the books that have sold less than 10 copies in the last year, excluding books that have been available for less than 1 month from today. Assume today is 2019-06-23.

The query result format is in the following example:

Books table:

   +---------+--------------------+----------------+
   | book_id | name               | available_from |
   +---------+--------------------+----------------+
   | 1       | "Kalila And Demna" | 2010-01-01     |
   | 2       | "28 Letters"       | 2012-05-12     |
   | 3       | "The Hobbit"       | 2019-06-10     |
   | 4       | "13 Reasons Why"   | 2019-06-01     |
   | 5       | "The Hunger Games" | 2008-09-21     |
  +---------+--------------------+----------------+

Orders table:

 +----------+---------+----------+---------------+
 | order_id | book_id | quantity | dispatch_date |
 +----------+---------+----------+---------------+
 | 1        | 1       | 2        | 2018-07-26    |
 | 2        | 1       | 1        | 2018-11-05    |
 | 3        | 3       | 8        | 2019-06-11    |
 | 4        | 4       | 6        | 2019-06-05    |
 | 5        | 4       | 5        | 2019-06-20    |
 | 6        | 5       | 9        | 2009-02-02    |
 | 7        | 5       | 8        | 2010-04-13    |
 +----------+---------+----------+---------------+

Result table:

+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/unpopular-books
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

答案

select b.book_id,b.name
from Books b left  join Orders o on b.book_id=o.book_id
where available_from<"2019-05-23"
group by b.book_id
having ifnull(sum(if(o.dispatch_date<"2018-06-23",quantity=0,quantity)),0)<10

反思

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
IFNULL() 函数语法格式为：
IFNULL(expression, alt_value)

最后编辑于：2019.08.06 16:14:37

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,258评论 6赞 498
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,335评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,225评论 0赞 353
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,126评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,140评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,098评论 1赞 295
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,018评论 3赞 417
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,857评论 0赞 273
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,298评论 1赞 310
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,518评论 2赞 332
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,678评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,400评论 5赞 343
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,993评论 3赞 325
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,638评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,801评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,661评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,558评论 2赞 352