1.部門工資前3高的員工
Employee 表包含所有員工信息，每個員工有其對應(yīng)的 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 查詢埋嵌，找出每個部門工資前三高的員工破加。例如，根據(jù)上述給定的表格雹嗦，查詢結(jié)果應(yīng)返回：
+------------+----------+--------+
| 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 語句應(yīng)返回如下結(jié)果臀稚，取消率（Cancellation Rate）保留兩位小數(shù)。
+------------+-------------------+
| 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)散劫。
請編寫一個查詢語句，找出高峰期時段幕帆，要求連續(xù)三天及以上获搏，并且每天人流量均不少于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 |
+------+------------+-----------+
對于上面的示例數(shù)據(jù)常熙，輸出為：
+------+------------+-----------+
| 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
吐槽一句 這都是什么神仙題啊,,, 還是我太弱雞了裸卫，
