一
題目
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 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個 SQL 查詢，找出每個部門工資最高的員工。例如，根據(jù)上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/department-highest-salary
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
我的答案
select Department.name Department,Employee.name Employee,max(Salary) as Salary
from Employee join Department on Employee.DepartmentId=Department.id
group by Department.Id
存在問題
當(dāng)一個部門，有兩個人工資相同高時(shí)，不適用。
參考答案
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表包含所有員工信息，每個員工有其對應(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 |
+------------+----------+--------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/department-top-three-salaries
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
參考答案：
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 |
+---------+------------------+------------------+
例如，根據(jù)上述給定的 Weather 表格，返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
我的解答
錯誤
正確答案
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()函數(shù)，round()函數(shù)
題目
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 |
+------------+-------------------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/trips-and-users
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
代碼
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(),統(tǒng)計(jì)符合條件的行，除了count(*)外會跳過空值的行
- sum
1.sum() 函數(shù)用于計(jì)算某一字段中所有行的數(shù)值之和（ sum 求和時(shí)會對 null 進(jìn)行過濾，不計(jì)算）
2.使用 sum(條件) 進(jìn)行對符合條件的結(jié)果行數(shù)進(jìn)行求和;
select sum(user_id is null),sum(aid = 1) from user_auth;
#如果沒有符合條件的元組，返回null,不是0
五
題目
X 市建了一個新的體育館，每日人流量信息被記錄在這三列信息中：序號 (id)、日期 (visit_date)、 人流量 (people)。
請編寫一個查詢語句，找出人流量的高峰期。高峰期時(shí)，至少連續(xù)三行記錄中的人流量不少于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 |
+------+------------+-----------+
對于上面的示例數(shù)據(jù)，輸出為：
+------+------------+-----------+
| 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
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
我的答案（微笑）
(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;
六
題目
小美是一所中學(xué)的信息科技老師，她有一張 seat 座位表，平時(shí)用來儲存學(xué)生名字和與他們相對應(yīng)的座位 id。其中縱列的 id 是連續(xù)遞增的小美想改變相鄰倆學(xué)生的座位。你能不能幫她寫一個 SQL query 來輸出小美想要的結(jié)果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如數(shù)據(jù)輸入的是上表，則輸出結(jié)果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果學(xué)生人數(shù)是奇數(shù)，則不需要改變最后一個同學(xué)的座位。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/exchange-seats
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
答案
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;
反思
七
題目
好評率是會員對平臺評價(jià)的重要指標(biāo)?，F(xiàn)在需要統(tǒng)計(jì)2018年1月1日到2018年1月31日，用戶'小明'提交的母嬰類目"花王"品牌的好評率（好評率=“好評”評價(jià)量/總評價(jià)量）:
用戶評價(jià)詳情表：a
字段：id（評價(jià)id，主鍵），
create_time（評價(jià)創(chuàng)建時(shí)間，格式'2017-01-01'），
user_name(用戶名稱)，
goods_id(商品id，外鍵) ，
sub_time（評價(jià)提交時(shí)間，格式'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函數(shù)返回一個值類型的數(shù)值，如果category=1,則返回size,如果category不等于1就返回0。 -
count(if(category=1,true,null))中count函數(shù)返回一個布爾值類型的數(shù)值，如果category=1,返回true,如果category不等于1返回null，如果寫成count(If(category=1,1,0) 則返回的全是true,也就是說全都會計(jì)數(shù)，而count()間斷內(nèi)容是true還是null,如果不是null就計(jì)數(shù)，如果是null就不計(jì)數(shù)。
所以count(if())的寫法應(yīng)該是count(if(表達(dá)式表達(dá)式，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
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
答案
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() 函數(shù)用于判斷第一個表達(dá)式是否為 NULL，如果為 NULL 則返回第二個參數(shù)的值，如果不為 NULL 則返回第一個參數(shù)的值。
IFNULL() 函數(shù)語法格式為：
IFNULL(expression, alt_value)
