Leetcode SQL 测试题困难篇

1.部门工资前3高的员工
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 |
+------------+----------+--------+
input：

{"headers": {"Employee": ["Id", "Name", "Salary", "DepartmentId"], "Department": ["Id", "Name"]}, "rows": {"Employee": [[1, "Joe", 70000, 1], [2, "Henry", 80000, 2], [3, "Sam", 60000, 2], [4, "Max", 90000, 1]], "Department": [[1, "IT"], [2, "Sales"]]}}

SELECT Department.Name AS Department, Employee.Name AS Employee, Salary
FROM Department INNER JOIN Employee
ON Department.Id = Employee.DepartmentId
AND Salary IN (SELECT DISTINCT TOP 3 Salary
                        FROM Employee
                        WHERE DepartmentId=Department.Id
                        ORDER BY Salary DESC
                        )
ORDER BY Department,Salary DESC

output:

{"headers":["Department","Employee","Salary"],"values":[["IT","Max",90000],["IT","Joe",70000],["Sales","Henry",80000],["Sales","Sam",60000]]}

这个题和中等难度里求部门最高工资的那个题差不多，要考虑到工资会有相同的情况，所以前三高工资注意要去重DISTINCT TOP 3;

  1. 行程和用户
    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 |
    +------------+-------------------+
    input:
{"headers": {"Trips": ["Id", "Client_Id", "Driver_Id", "City_Id", "Status", "Request_at"], "Users": ["Users_Id", "Banned", "Role"]}, "rows": {"Trips": [["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": [["1", "No", "client"], ["2", "Yes", "client"], ["3", "No", "client"], ["4", "No", "client"], ["10", "No", "driver"], ["11", "No", "driver"], ["12", "No", "driver"], ["13", "No", "driver"]]}}

/* Write your T-SQL query statement below */
SELECT C.Day, ISNULL(ROUND(CAST(C.cancel_num AS FLOAT) / CAST(C.all_num AS FLOAT), 2),0) AS 'Cancellation Rate'
FROM 
(
SELECT A.Request_at AS Day, B.cancel_num, A.all_num
FROM
    (SELECT Request_at, COUNT(*) AS all_num
    FROM Trips, Users U1, Users U2
    WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
    AND (U1.Banned = 'NO') AND (U2.Banned = 'NO')
    AND (Client_Id = U1.Users_Id) AND (Driver_Id = U2.Users_Id)
    GROUP BY Trips.Request_at) AS A
LEFT JOIN
    (SELECT Request_at, COUNT(*) AS cancel_num
    FROM Trips, Users U1,Users U2
    WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
    AND (U1.Banned = 'NO') AND (U2.Banned = 'NO')
    AND (Client_Id = U1.Users_Id) AND (Driver_Id = U2.Users_Id)
    AND (Status <> 'completed')
    GROUP BY Trips.Request_at) AS B
ON A.Request_at = B.Request_at
)C

{"headers":["Day","Cancellation Rate"],"values":[["2013-10-01",0.33],["2013-10-02",0.0],["2013-10-03",0.5]]}
  1. 体育馆的人流量
    X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。
    请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。
    例如，表 stadium：
    +------+------------+-----------+
    | id | 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 | date | people |
    +------+------------+-----------+
    | 5 | 2017-01-05 | 145 |
    | 6 | 2017-01-06 | 1455 |
    | 7 | 2017-01-07 | 199 |
    | 8 | 2017-01-08 | 188 |
    +------+------------+-----------+
    Note:
    每天只有一行记录，日期随着 id 的增加而增加。
    input:
{"headers": {"stadium": ["id", "date", "people"]}, "rows": {"stadium": [[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]]}}

SELECT DISTINCT s4.id,s4.date,s4.people 
FROM stadium s1,stadium s2,stadium s3,stadium s4 
WHERE s1.id+1=s2.id AND s2.id+1=s3.id 
AND s1.people>=100 AND s2.people>=100 AND s3.people>=100 
AND s4.id IN (s1.id,s2.id,s3.id)

output:

{"headers":["id","date","people"],"values":[[5,"2017-01-05",145],[6,"2017-01-06",1455],[7,"2017-01-07",199],[8,"2017-01-08",188]]}

END

吐槽一句 这都是什么神仙题啊,,, 还是我太弱鸡了，

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

推荐阅读更多精彩内容

  • Leetcode Datebase Problem(2)
    183. Customers Who Never Order Problem Suppose that a web...
    olivia_ong阅读 378评论 0赞 0
  • 2018-09-25
    2018-11-12 00:10:21 +0900 Notification Display is turned ...
    hjpotter1阅读 2,693评论 1赞 49
  • sql刷题
    262.Trips and Users 难题 case when 记得加end， between如果不是数字的话记...
    吃面米糕阅读 512评论 0赞 0
  • 信仰
    信仰 我和你面对面坐着 你有半边脸颊是白的 我看着黑夜在蔓延 眼睛没有闪躲的昨天 我相信你不怕下雨 因为下雨日子才...
    月階霜滿除阅读 185评论 6赞 2
  • 刘小白的高跟鞋
    陆天和刘小白分手的原因至今让刘小白反胃。 当年，陆天爱刘小白也算爱得轰轰烈烈，陆天最爱的是刘小白的“高跟鞋范儿”，...
    浮生有涯阅读 305评论 0赞 0