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;
- 行程和用户
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]]}
- 体育馆的人流量
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
吐槽一句 这都是什么神仙题啊,,, 还是我太弱鸡了，
网友评论