題目選自leetcode 上的題庫

可能不一定都是最優(yōu)解牵啦，答案僅供參考

每道題后面都應(yīng)相應(yīng)的難度等級紧憾，如果沒時間做的話 可以在leetcode 按出題頻率刷題

祝大家面試取得好的成績

585. 2016年的投資

難度中等14收藏分享切換為英文關(guān)注反饋

SQL架構(gòu)

寫一個查詢語句雳锋，將 2016 年 (TIV_2016) 所有成功投資的金額加起來，保留 2 位小數(shù)。

對于一個投保人，他在 2016 年成功投資的條件是：

1. 他在 2015 年的投保額 (TIV_2015) 至少跟一個其他投保人在 2015 年的投保額相同便瑟。
2. 他所在的城市必須與其他投保人都不同（也就是說維度和經(jīng)度不能跟其他任何一個投保人完全相同）坟募。

輸入格式:
表 *insurance* 格式如下：

PID 字段是投保人的投保編號岛蚤， TIV_2015 是該投保人在2015年的總投保金額， TIV_2016 是該投保人在2016年的投保金額懈糯， LAT 是投保人所在城市的維度涤妒， LON 是投保人所在城市的經(jīng)度。

樣例輸入

樣例輸出

解釋

就如最后一個投保人赚哗，第一個投保人同時滿足兩個條件：
1. 他在 2015 年的投保金額 TIV_2015 為 '10' 她紫，與第三個和第四個投保人在 2015 年的投保金額相同。
2. 他所在城市的經(jīng)緯度是獨一無二的屿储。

第二個投保人兩個條件都不滿足贿讹。他在 2015 年的投資 TIV_2015 與其他任何投保人都不相同。
且他所在城市的經(jīng)緯度與第三個投保人相同够掠∶窆樱基于同樣的原因，第三個投保人投資失敗疯潭。

所以返回的結(jié)果是第一個投保人和最后一個投保人的 TIV_2016 之和助赞，結(jié)果是 45 。

select sum(TIV_2016) TIV_2016
from (
    select PID,TIV_2016,cnt,
    count(*) over(partition by loc ) lcnt
    from (
        select PID,TIV_2016,
        count(TIV_2015) over(partition by TIV_2015 ) cnt,
        concat_ws(",",LAT,LON) loc
        from insurance 
    )t1
)t2
where lcnt=1 and cnt!=1

注意去重順序 不要先對TIV_2015去重 不然 local去重時會丟失數(shù)據(jù)

優(yōu)化 窗口

SELECT 
    ROUND(SUM(TIV_2016), 2) as TIV_2016
FROM(
    SELECT
        *,
        count(*) over(partition by TIV_2015) as cnt_1,
        count(*) over(partition by LAT, LON) as cnt_2
    FROM
        insurance
) a 
WHERE a.cnt_1 > 1 AND a.cnt_2 < 2

586. 訂單最多的客戶

難度簡單

SQL架構(gòu)

在表 orders 中找到訂單數(shù)最多客戶對應(yīng)的 customer_number 袁勺。

數(shù)據(jù)保證訂單數(shù)最多的顧客恰好只有一位。

表 *orders* 定義如下：

樣例輸入

樣例輸出

解釋

customer_number 為 '3' 的顧客有兩個訂單畜普，比顧客 '1' 或者 '2' 都要多期丰，因為他們只有一個訂單
所以結(jié)果是該顧客的 customer_number ，也就是 3 吃挑。

進階： 如果有多位顧客訂單數(shù)并列最多钝荡，你能找到他們所有的 customer_number 嗎？

select customer_number
from orders
group by customer_number 
order by count(*)  desc
limit 1

如果 數(shù)據(jù)量很大 order by 不太好

595. 大的國家

難度簡單

SQL架構(gòu)

這里有張 World 表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一個國家的面積超過300萬平方公里舶衬，或者人口超過2500萬埠通，那么這個國家就是大國家。

編寫一個SQL查詢逛犹，輸出表中所有大國家的名稱端辱、人口和面積。

例如虽画，根據(jù)上表舞蔽，我們應(yīng)該輸出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

select  name ,population,area  
from World
where area  >3000000 or population >25000000

596. 超過5名學(xué)生的課

難度簡單

SQL架構(gòu)

有一個courses 表 ，有: student (學(xué)生) 和 class (課程)码撰。

請列出所有超過或等于5名學(xué)生的課渗柿。

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

應(yīng)該輸出:

+---------+
| class   |
+---------+
| Math    |
+---------+

Note:
學(xué)生在每個課中不應(yīng)被重復(fù)計算。

select  class 
from courses
group by class 
having count(distinct student)>=5

一個學(xué)生可能多次選課脖岛。朵栖。記得distinct

597. 好友申請 I ：總體通過率

難度簡單21收藏分享切換為英文關(guān)注反饋

SQL架構(gòu)

在 Facebook 或者 Twitter 這樣的社交應(yīng)用中颊亮，人們經(jīng)常會發(fā)好友申請也會收到其他人的好友申請。現(xiàn)在給如下兩個表：

表： friend_request

表： request_accepted

寫一個查詢語句陨溅，求出好友申請的通過率终惑，用 2 位小數(shù)表示。通過率由接受好友申請的數(shù)目除以申請總數(shù)声登。

對于上面的樣例數(shù)據(jù)狠鸳，你的查詢語句應(yīng)該返回如下結(jié)果。

注意:

通過的好友申請不一定都在表 friend_request 中悯嗓。在這種情況下件舵，你只需要統(tǒng)計總的被通過的申請數(shù)（不管它們在不在原來的申請中），并將它除以申請總數(shù)脯厨，得到通過率
一個好友申請發(fā)送者有可能會給接受者發(fā)幾條好友申請铅祸，也有可能一個好友申請會被通過好幾次。這種情況下合武，重復(fù)的好友申請只統(tǒng)計一次临梗。
如果一個好友申請都沒有，通過率為 0.00 稼跳。

解釋： 總共有 5 個申請盟庞，其中 4 個是不重復(fù)且被通過的好友申請，所以成功率是 0.80 汤善。

進階:

你能寫一個查詢語句得到每個月的通過率嗎什猖？
你能求出每一天的累計通過率嗎？

select
round(
    ifnull(
    (select count(*) from (select distinct requester_id, accepter_id from request_accepted) as A)
    /
    (select count(*) from (select distinct sender_id, send_to_id from friend_request) as B),
    0)
, 2) as accept_rate;

601. 體育館的人流量

難度困難113收藏分享切換為英文關(guān)注反饋

SQL架構(gòu)

X 市建了一個新的體育館红淡，每日人流量信息被記錄在這三列信息中：序號 (id)不狮、日期 (visit_date)、 人流量 (people)在旱。

請編寫一個查詢語句摇零，找出人流量的高峰期。高峰期時桶蝎，至少連續(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 的增加而增加。

3表相連(244 ms)

select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
        (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)  -- t1, t2, t3
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id

窗口函數(shù)(272 ms)

select id,visit_date,people from
(
    select id
    ,lead(people,1) over(order by id) ld
    ,lead(people,2) over(order by id) ld2
    ,visit_date
    ,lag(people,1) over(order by id) lg
    ,lag(people,2) over(order by id) lg2
    ,people
    from stadium
    ) a
where (a.ld>=100 and a.lg>=100 and a.people>=100)
or (a.ld>=100 and a.ld2>=100 and a.people>=100)
or (a.lg>=100 and a.lg2>=100 and a.people>=100)

602. 好友申請 II ：誰有最多的好友

難度中等

SQL架構(gòu)

在 Facebook 或者 Twitter 這樣的社交應(yīng)用中绍豁，人們經(jīng)常會發(fā)好友申請也會收到其他人的好友申請芯咧。

表 request_accepted 存儲了所有好友申請通過的數(shù)據(jù)記錄，其中， requester_id 和 accepter_id 都是用戶的編號敬飒。

寫一個查詢語句邪铲，求出誰擁有最多的好友和他擁有的好友數(shù)目。對于上面的樣例數(shù)據(jù)无拗，結(jié)果為：

注意：

• 保證擁有最多好友數(shù)目的只有 1 個人带到。
• 好友申請只會被接受一次，所以不會有 requester_id 和 accepter_id 值都相同的重復(fù)記錄英染。

解釋：

編號為 '3' 的人是編號為 '1'揽惹，'2' 和 '4' 的好友，所以他總共有 3 個好友四康，比其他人都多搪搏。

進階：

在真實世界里，可能會有多個人擁有好友數(shù)相同且最多闪金，你能找到所有這些人嗎疯溺？

select rid as `id`,count(aid) as `num`
from
(
    select R1.requester_id as rid,R1.accepter_id as aid
    from request_accepted as R1
    UNION all
    select R2.accepter_id as rid,R2.requester_id as aid
    from request_accepted as R2
) as A
group by rid
order by num desc
limit 0,1

603. 連續(xù)空余座位

難度簡單

SQL架構(gòu)

幾個朋友來到電影院的售票處，準(zhǔn)備預(yù)約連續(xù)空余座位哎垦。

你能利用表 cinema 囱嫩，幫他們寫一個查詢語句，獲取所有空余座位漏设，并將它們按照 seat_id 排序后返回嗎墨闲？

對于如上樣例，你的查詢語句應(yīng)該返回如下結(jié)果郑口。

注意：

• seat_id 字段是一個自增的整數(shù)鸳碧，free 字段是布爾類型（'1' 表示空余， '0' 表示已被占據(jù)）潘酗。
• 連續(xù)空余座位的定義是大于等于 2 個連續(xù)空余的座位。

select seat_id
from (
select seat_id,
lag(seat_id,1,-99) over(order by seat_id) ls,
lead(seat_id,1,-99) over(order by seat_id) rs
from cinema
where free=1
)t1
where  seat_id-ls = 1 or rs-seat_id =1

607. 銷售員

難度簡單

SQL架構(gòu)

描述

給定 3 個表： salesperson雁仲， company仔夺， orders。
輸出所有表 salesperson 中攒砖，沒有向公司 'RED' 銷售任何東西的銷售員缸兔。

示例：
輸入

表： salesperson

+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+

表 salesperson 存儲了所有銷售員的信息。每個銷售員都有一個銷售員編號 sales_id 和他的名字 name 吹艇。

表： company

+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+

表 company 存儲了所有公司的信息惰蜜。每個公司都有一個公司編號 com_id 和它的名字 name 。

表： orders

+----------+------------+---------+----------+--------+
| order_id | order_date | com_id  | sales_id | amount |
+----------+------------+---------+----------+--------+
| 1        |   1/1/2014 |    3    |    4     | 100000 |
| 2        |   2/1/2014 |    4    |    5     | 5000   |
| 3        |   3/1/2014 |    1    |    1     | 50000  |
| 4        |   4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+

表 orders 存儲了所有的銷售數(shù)據(jù)受神，包括銷售員編號 sales_id 和公司編號 com_id 抛猖。

輸出

+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+

解釋

根據(jù)表 orders 中的訂單 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 兩個銷售員曾經(jīng)向公司 'RED' 銷售過。

所以我們需要輸出表 salesperson 中所有其他人的名字财著。\

select name
from salesperson
where sales_id not in
(
    select sales_id
    from orders
    where com_id =
            (
            select com_id 
            from company
            where name ='RED'
            )
)

608. 樹節(jié)點

難度中等

SQL架構(gòu)

給定一個表 tree联四，id 是樹節(jié)點的編號， p_id 是它父節(jié)點的 id 撑教。

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

樹中每個節(jié)點屬于以下三種類型之一：

• 葉子：如果這個節(jié)點沒有任何孩子節(jié)點朝墩。
• 根：如果這個節(jié)點是整棵樹的根，即沒有父節(jié)點伟姐。
• 內(nèi)部節(jié)點：如果這個節(jié)點既不是葉子節(jié)點也不是根節(jié)點收苏。

寫一個查詢語句，輸出所有節(jié)點的編號和節(jié)點的類型愤兵，并將結(jié)果按照節(jié)點編號排序鹿霸。上面樣例的結(jié)果為：

+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

解釋

• 節(jié)點 '1' 是根節(jié)點，因為它的父節(jié)點是 NULL 恐似，同時它有孩子節(jié)點 '2' 和 '3' 杜跷。

• 節(jié)點 '2' 是內(nèi)部節(jié)點，因為它有父節(jié)點 '1' 矫夷，也有孩子節(jié)點 '4' 和 '5' 葛闷。

• 節(jié)點 '3', '4' 和 '5' 都是葉子節(jié)點，因為它們都有父節(jié)點同時沒有孩子節(jié)點双藕。

• 樣例中樹的形態(tài)如下：

          1
        /   \
        2       3
      /   \
   4       5
  

注意

如果樹中只有一個節(jié)點淑趾，你只需要輸出它的根屬性。

select id,
    (case when p_id is null then "Root"
    when id not in (select ifnull(p_id,0) from tree) then "Leaf"
    else "Inner" end)  Type
from tree

610. 判斷三角形

難度簡單

SQL架構(gòu)

一個小學(xué)生 Tim 的作業(yè)是判斷三條線段是否能形成一個三角形忧陪。

然而扣泊，這個作業(yè)非常繁重，因為有幾百組線段需要判斷嘶摊。

假設(shè)表 triangle 保存了所有三條線段的三元組 x, y, z 延蟹，你能幫 Tim 寫一個查詢語句，來判斷每個三元組是否可以組成一個三角形嗎叶堆？

對于如上樣例數(shù)據(jù)阱飘，你的查詢語句應(yīng)該返回如下結(jié)果：

select x,y,z, 
if(x+y>z && x+z>y && y+z>x,'Yes','No') triangle
from triangle

612. 平面上的最近距離

難度中等

SQL架構(gòu)

表 point_2d 保存了所有點（多于 2 個點）的坐標(biāo) (x,y) ，這些點在平面上兩兩不重合虱颗。

寫一個查詢語句找到兩點之間的最近距離沥匈，保留 2 位小數(shù)。

最近距離在點 (-1,-1) 和(-1,2) 之間忘渔，距離為 1.00 高帖。所以輸出應(yīng)該為：

注意：任意點之間的最遠距離小于 10000 。

SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))), 2) AS shortest
FROM
    point_2d p1
        JOIN
    point_2d p2 ON p1.x != p2.x OR p1.y != p2.y

優(yōu)化 ：減少重復(fù)計算

SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))),2) AS shortest
FROM
    point_2d p1
        JOIN
    point_2d p2 ON (p1.x <= p2.x AND p1.y < p2.y)
        OR (p1.x <= p2.x AND p1.y > p2.y)
        OR (p1.x < p2.x AND p1.y = p2.y)

613. 直線上的最近距離

難度簡單

SQL架構(gòu)

表 point 保存了一些點在 x 軸上的坐標(biāo)畦粮，這些坐標(biāo)都是整數(shù)散址。

寫一個查詢語句乖阵，找到這些點中最近兩個點之間的距離。

最近距離顯然是 '1' 爪飘，是點 '-1' 和 '0' 之間的距離义起。所以輸出應(yīng)該如下：

注意：每個點都與其他點坐標(biāo)不同，表 table 不會有重復(fù)坐標(biāo)出現(xiàn)师崎。

進階：如果這些點在 x 軸上從左到右都有一個編號默终，輸出結(jié)果時需要輸出最近點對的編號呢？

開窗方法 178m

select min(l-x) shortest
from( 
select x,lead(x,1,null) over(order by x) l
from point
)t1

join方法 268m

SELECT
    MIN(ABS(p1.x - p2.x)) AS shortest
FROM
    point p1
        JOIN
    point p2 ON p1.x != p2.x
;

614. 二級關(guān)注者

難度中等

SQL架構(gòu)

在 facebook 中犁罩，表 follow 會有 2 個字段： followee, follower 齐蔽，分別表示被關(guān)注者和關(guān)注者。

請寫一個 sql 查詢語句床估，對每一個關(guān)注者含滴，查詢關(guān)注他的關(guān)注者的數(shù)目。

比方說：

+-------------+------------+
| followee    | follower   |
+-------------+------------+
|     A       |     B      |
|     B       |     C      |
|     B       |     D      |
|     D       |     E      |
+-------------+------------+

應(yīng)該輸出：

+-------------+------------+
| follower    | num        |
+-------------+------------+
|     B       |  2         |
|     D       |  1         |
+-------------+------------+

解釋：

B 和 D 都在在 follower 字段中出現(xiàn)丐巫，作為被關(guān)注者谈况，B 被 C 和 D 關(guān)注，D 被 E 關(guān)注递胧。A 不在 follower 字段內(nèi)碑韵，所以A不在輸出列表中。

注意：

• 被關(guān)注者永遠不會被他 / 她自己關(guān)注缎脾。
• 將結(jié)果按照字典序返回祝闻。

select followee follower,count(distinct follower) num
from follow
where followee  in (
    select follower
    from follow
    group by follower
)
group by followee
order by follower 

這里出現(xiàn)了重復(fù)關(guān)注 ，需要去重

615. 平均工資：部門與公司比較

難度困難

SQL架構(gòu)

給如下兩個表遗菠，寫一個查詢語句联喘，求出在每一個工資發(fā)放日，每個部門的平均工資與公司的平均工資的比較結(jié)果 （高 / 低 / 相同）辙纬。

表： salary

employee_id 字段是表 employee 中 employee_id 字段的外鍵豁遭。

對于如上樣例數(shù)據(jù)，結(jié)果為：

解釋

在三月贺拣，公司的平均工資是 (9000+6000+10000)/3 = 8333.33...

由于部門 '1' 里只有一個 employee_id 為 '1' 的員工蓖谢，所以部門 '1' 的平均工資就是此人的工資 9000 。因為 9000 > 8333.33 纵柿，所以比較結(jié)果是 'higher'蜈抓。

第二個部門的平均工資為 employee_id 為 '2' 和 '3' 兩個人的平均工資启绰，為 (6000+10000)/2=8000 昂儒。因為 8000 < 8333.33 ，所以比較結(jié)果是 'lower' 委可。

在二月用同樣的公式求平均工資并比較渊跋，比較結(jié)果為 'same' 腊嗡，因為部門 '1' 和部門 '2' 的平均工資與公司的平均工資相同，都是 7000 拾酝。

select 
    pay_month,
    department_id,
     (case when avgs>ts then 'higher'
            when avgs<ts then 'lower'
            else 'same' end) as comparison
from 
(
    select 
        date_format(pay_date,'%Y-%m')pay_month,
        department_id,
        avg(amount) over(partition by date_format(pay_date,'%Y-%m') )ts,
        avg(amount) over(partition by date_format(pay_date,'%Y-%m'),department_id) avgs
    from salary s
    left join employee e
    on s.employee_id = e.employee_id
   
)t1
 group by pay_month, department_id

也可以用if

IF(avgs>ts,'higher',IF(avgs=ts,'same','lower')) AS comparison

618. 學(xué)生地理信息報告

難度困難

SQL架構(gòu)

一所美國大學(xué)有來自亞洲燕少、歐洲和美洲的學(xué)生，他們的地理信息存放在如下 student 表中蒿囤。

寫一個查詢語句實現(xiàn)對大洲（continent）列的 透視表 操作客们，使得每個學(xué)生按照姓名的字母順序依次排列在對應(yīng)的大洲下面。輸出的標(biāo)題應(yīng)依次為美洲（America）材诽、亞洲（Asia）和歐洲（Europe）底挫。數(shù)據(jù)保證來自美洲的學(xué)生不少于來自亞洲或者歐洲的學(xué)生。

對于樣例輸入脸侥，它的對應(yīng)輸出是：

進階：如果不能確定哪個大洲的學(xué)生數(shù)最多建邓，你可以寫出一個查詢?nèi)ド缮鲜鰧W(xué)生報告嗎？

開窗

select
max(if(continent='America',name,null)) America,
max(if(continent='Asia',name,null)) Asia,
max(if(continent='Europe',name,null)) Europe
from 
    (select *, row_number() over(partition by continent order by name) rk
    from student) t
group by rk

變量

SELECT 
    America, Asia, Europe
FROM
    (SELECT @as:=0, @am:=0, @eu:=0) t,
    (SELECT 
        @as:=@as + 1 AS asid, name AS Asia
    FROM
        student
    WHERE
        continent = 'Asia'
    ORDER BY Asia) AS t1
        RIGHT JOIN
    (SELECT 
        @am:=@am + 1 AS amid, name AS America
    FROM
        student
    WHERE
        continent = 'America'
    ORDER BY America) AS t2 ON asid = amid
        LEFT JOIN
    (SELECT 
        @eu:=@eu + 1 AS euid, name AS Europe
    FROM
        student
    WHERE
        continent = 'Europe'
    ORDER BY Europe) AS t3 ON amid = euid

官方給出的睁枕。官边。同下方開窗

select America,Asia,Europe 
from(
    select row_number() over(order by name) as rn,name as America from student
    where continent='America'
) a
left join(
    select row_number() over(order by name) as rn,name as Asia from student
    where continent='Asia'
) b on a.rn=b.rn
left join(
    select row_number() over(order by name) as rn,name as Europe from student
    where continent='Europe'
) c on a.rn=c.rn

619. 只出現(xiàn)一次的最大數(shù)字

難度簡單

SQL架構(gòu)

表 my_numbers 的 num 字段包含很多數(shù)字，其中包括很多重復(fù)的數(shù)字外遇。

你能寫一個 SQL 查詢語句注簿，找到只出現(xiàn)過一次的數(shù)字中，最大的一個數(shù)字嗎臀规？

+---+
|num|
+---+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 | 

對于上面給出的樣例數(shù)據(jù)滩援，你的查詢語句應(yīng)該返回如下結(jié)果：

+---+
|num|
+---+
| 6 |

注意：

如果沒有只出現(xiàn)一次的數(shù)字，輸出 null 塔嬉。

SELECT
    MAX(num) AS num
FROM
    (SELECT
        num
    FROM
        my_numbers
    GROUP BY num
    HAVING COUNT(num) = 1) t1

620. 有趣的電影

難度簡單86收藏分享切換為英文關(guān)注反饋

SQL架構(gòu)

某城市開了一家新的電影院玩徊，吸引了很多人過來看電影。該電影院特別注意用戶體驗谨究，專門有個 LED顯示板做電影推薦恩袱，上面公布著影評和相關(guān)電影描述。

作為該電影院的信息部主管胶哲，您需要編寫一個 SQL查詢畔塔，找出所有影片描述為非 boring (不無聊) 的并且 id 為奇數(shù) 的影片，結(jié)果請按等級 rating 排列鸯屿。

例如澈吨，下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

對于上面的例子，則正確的輸出是為：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

select  id,movie,description,rating 
from cinema
where id%2=1 and description !='boring'
order by rating desc,id,movie,description

626. 換座位

難度中等

SQL架構(gòu)

小美是一所中學(xué)的信息科技老師寄摆，她有一張 seat 座位表谅辣，平時用來儲存學(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é)的座位蚣录。

開窗

select id,
(case when id%2=0 then f
      when id%2=1 && b is not null then b
      else student end) student
from(
    select id,student,
    lag(student,1,null) over(order by id) f,
    lead(student,1,null) over(order by id) b
    from seat
)t1

非嵌套

select 
    if(id%2=0,
        id-1,
        if(id=(select count(distinct id) from seat),
            id,
            id+1)) 
    as id,student 
from seat 
order by id;

用異或

select b.id,a.student from 
seat as a,seat as b,(select count(*) as cnt from seat) as c 
where b.id=1^(a.id-1)+1
-- where a.id=1^(b.id-1)+1; 也可以這樣寫割择，更容易理解
 || (c.cnt%2 && b.id=c.cnt && a.id=c.cnt);

627. 交換工資

難度簡單

SQL架構(gòu)

給定一個 salary 表，如下所示萎河，有 m = 男性 和 f = 女性 的值荔泳。交換所有的 f 和 m 值（例如，將所有 f 值更改為 m虐杯，反之亦然）换可。要求只使用一個更新（Update）語句，并且沒有中間的臨時表厦幅。

注意沾鳄，您必只能寫一個 Update 語句，請不要編寫任何 Select 語句确憨。

例如：

運行你所編寫的更新語句之后译荞，將會得到以下表:

UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

Update 和set的使用

1045. 買下所有產(chǎn)品的客戶

難度中等

SQL架構(gòu)

Customer 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+
product_key 是 Customer 表的外鍵。

Product 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_key | int     |
+-------------+---------+
product_key 是這張表的主鍵休弃。

寫一條 SQL 查詢語句吞歼，從 Customer 表中查詢購買了 Product 表中所有產(chǎn)品的客戶的 id。

示例：

Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+

Product 表：
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+

Result 表：
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
購買了所有產(chǎn)品（5 和 6）的客戶的 id 是 1 和 3 塔猾。

select customer_id
from Customer
group by customer_id
having count(distinct product_key)=(
select count(*) cnt
from Product)

1050. 合作過至少三次的演員和導(dǎo)演

難度簡單

SQL架構(gòu)

ActorDirector 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| actor_id    | int     |
| director_id | int     |
| timestamp   | int     |
+-------------+---------+
timestamp 是這張表的主鍵.

寫一條SQL查詢語句獲取合作過至少三次的演員和導(dǎo)演的 id 對 (actor_id, director_id)

示例：

ActorDirector 表：
+-------------+-------------+-------------+
| actor_id    | director_id | timestamp   |
+-------------+-------------+-------------+
| 1           | 1           | 0           |
| 1           | 1           | 1           |
| 1           | 1           | 2           |
| 1           | 2           | 3           |
| 1           | 2           | 4           |
| 2           | 1           | 5           |
| 2           | 1           | 6           |
+-------------+-------------+-------------+

Result 表：
+-------------+-------------+
| actor_id    | director_id |
+-------------+-------------+
| 1           | 1           |
+-------------+-------------+
唯一的 id 對是 (1, 1)篙骡，他們恰好合作了 3 次。

select actor_id,director_id
from ActorDirector
group by actor_id,director_id
having count(*)>=3

1068. 產(chǎn)品銷售分析 I

難度簡單

SQL架構(gòu)

銷售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) 是銷售表 Sales 的主鍵.
product_id 是產(chǎn)品表 Product 的外鍵.
注意: price 表示每單位價格

產(chǎn)品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是表的主鍵.

寫一條SQL 查詢語句獲取產(chǎn)品表 Product 中所有的 產(chǎn)品名稱 product name 以及 該產(chǎn)品在 Sales 表中相對應(yīng)的 上市年份 year 和 價格 price丈甸。

示例：

Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+

Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+

Result 表：
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

select product_name,year,price
from Sales s left join Product p
on s.product_id  = p.product_id