leetcode數(shù)據(jù)庫刷題一

一

題目
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            |
        +----+-------+--------+--------------+

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
著作權歸領扣網(wǎng)絡所有得封。商業(yè)轉載請聯(lián)系官方授權埋心，非商業(yè)轉載請注明出處。
我的答案

select Department.name Department,Employee.name Employee,max(Salary) as Salary
from Employee join  Department on Employee.DepartmentId=Department.id
group by Department.Id

存在問題
當一個部門忙上，有兩個人工資相同高時拷呆，不適用。

參考答案

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表包含所有員工信息疫粥，每個員工有其對應的 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ù)上述給定的表格慷彤，查詢結果應返回：

         +------------+----------+--------+
         | 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
著作權歸領扣網(wǎng)絡所有娄蔼。商業(yè)轉載請聯(lián)系官方授權，非商業(yè)轉載請注明出處瞬欧。
參考答案：

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:

我的解答

錯誤

正確答案

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 語句應返回如下結果的妖，取消率（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
著作權歸領扣網(wǎng)絡所有足陨。商業(yè)轉載請聯(lián)系官方授權嫂粟，非商業(yè)轉載請注明出處。

代碼

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)計符合條件的行钠右，除了count(*)外會跳過空值的行
sum

1.sum() 函數(shù)用于計算某一字段中所有行的數(shù)值之和（ sum 求和時會對 null 進行過濾赋元，不計算）
2.使用 sum(條件) 進行對符合條件的結果行數(shù)進行求和;

    select sum(user_id is null),sum(aid = 1) from user_auth;
    #如果沒有符合條件的元組，返回null,不是0

五

題目

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 的增加而增加。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/human-traffic-of-stadium
著作權歸領扣網(wǎng)絡所有罕偎。商業(yè)轉載請聯(lián)系官方授權很澄，非商業(yè)轉載請注明出處。

我的答案（微笑）

(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;

六

題目

小美是一所中學的信息科技老師颜及，她有一張 seat 座位表甩苛，平時用來儲存學生名字和與他們相對應的座位 id。其中縱列的 id 是連續(xù)遞增的小美想改變相鄰倆學生的座位俏站。你能不能幫她寫一個 SQL query 來輸出小美想要的結果呢讯蒲？
示例：

      +---------+---------+
      |    id   | student |
     +---------+---------+
     |    1    | Abbot   |
     |    2    | Doris   |
     |    3    | Emerson |
     |    4    | Green   |
     |    5    | Jeames  |
    +---------+---------+

假如數(shù)據(jù)輸入的是上表，則輸出結果如下：

    +---------+---------+
    |    id   | student |
    +---------+---------+
    |    1    | Doris   |
    |    2    | Abbot   |
    |    3    | Green   |
    |    4    | Emerson |
    |    5    | Jeames  |
    +---------+---------+

注意：
如果學生人數(shù)是奇數(shù)肄扎，則不需要改變最后一個同學的座位墨林。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/exchange-seats
著作權歸領扣網(wǎng)絡所有赁酝。商業(yè)轉載請聯(lián)系官方授權，非商業(yè)轉載請注明出處旭等。

答案

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;

反思

七

題目

好評率是會員對平臺評價的重要指標≡藁現(xiàn)在需要統(tǒng)計2018年1月1日到2018年1月31日，用戶'小明'提交的母嬰類目"花王"品牌的好評率（好評率=“好評”評價量/總評價量）:
用戶評價詳情表：a
字段：id（評價id辆雾，主鍵），
create_time（評價創(chuàng)建時間月劈，格式'2017-01-01'）度迂，
user_name(用戶名稱)，
goods_id(商品id猜揪，外鍵) 惭墓，
sub_time（評價提交時間，格式'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,也就是說全都會計數(shù)万搔，而count()間斷內(nèi)容是true還是null,如果不是null就計數(shù)，如果是null就不計數(shù)官帘。

所以count(if())的寫法應該是count(if(表達式表達式瞬雹，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
著作權歸領扣網(wǎng)絡所有。商業(yè)轉載請聯(lián)系官方授權刽虹，非商業(yè)轉載請注明出處酗捌。

答案

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ù)用于判斷第一個表達式是否為 NULL，如果為 NULL 則返回第二個參數(shù)的值状婶，如果不為 NULL 則返回第一個參數(shù)的值意敛。
IFNULL() 函數(shù)語法格式為：
IFNULL(expression, alt_value)

最后編輯于：2019.08.06 16:14:37

?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市膛虫，隨后出現(xiàn)的幾起案子草姻，更是在濱河造成了極大的恐慌，老刑警劉巖稍刀，帶你破解...
沈念sama閱讀 222,729評論 6贊 517
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件撩独，死亡現(xiàn)場離奇詭異敞曹，居然都是意外死亡，警方通過查閱死者的電腦和手機综膀，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 95,226評論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門澳迫，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人剧劝，你說我怎么就攤上這事橄登。” “怎么了讥此？”我有些...
開封第一講書人閱讀 169,461評論 0贊 362
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵拢锹，是天一觀的道長。經(jīng)常有香客問我萄喳，道長卒稳，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 60,135評論 1贊 300
?港島之戀（遺憾婚禮）
正文為了忘掉前任他巨，我火速辦了婚禮充坑，結果婚禮上，老公的妹妹穿的比我還像新娘染突。我一直安慰自己捻爷，他們只是感情好，可當我...
茶點故事閱讀 69,130評論 6贊 398
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布份企。她就那樣靜靜地躺著役衡，像睡著了一般。火紅的嫁衣襯著肌膚如雪薪棒。梳的紋絲不亂的頭發(fā)上手蝎，一...
開封第一講書人閱讀 52,736評論 1贊 312
城市分裂傳說
那天，我揣著相機與錄音俐芯，去河邊找鬼棵介。笑死，一個胖子當著我的面吹牛吧史，可吹牛的內(nèi)容都是我干的邮辽。我是一名探鬼主播，決...
沈念sama閱讀 41,179評論 3贊 422
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼贸营，長吁一口氣：“原來是場噩夢啊……” “哼吨述！你這毒婦竟也來了？” 一聲冷哼從身側響起钞脂，我...
開封第一講書人閱讀 40,124評論 0贊 277
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤揣云，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后冰啃，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體邓夕，經(jīng)...
沈念sama閱讀 46,657評論 1贊 320
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡刘莹，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 38,723評論 3贊 342
?白月光啟示錄
正文我和宋清朗相戀三年焚刚，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片矿咕。...
茶點故事閱讀 40,872評論 1贊 353
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖碳柱，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情士聪，我是刑警寧澤，帶...
沈念sama閱讀 36,533評論 5贊 351
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布剥悟，位于F島的核電站，受9級特大地震影響区岗，放射性物質發(fā)生泄漏。R本人自食惡果不足惜毁枯，卻給世界環(huán)境...
茶點故事閱讀 42,213評論 3贊 336
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望藐鹤。院中可真熱鬧，春花似錦赂韵、人聲如沸娱节。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,700評論 0贊 25
一樁弒父案祭示，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽肄满。三九已至，卻和暖如春质涛，著一層夾襖步出監(jiān)牢的瞬間稠歉，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,819評論 1贊 274
情欲美人皮
我被黑心中介騙來泰國打工汇陆，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留怒炸，地道東北人。一個月前我還...
沈念sama閱讀 49,304評論 3贊 379
代替公主和親
正文我出身青樓毡代，卻偏偏與公主長得像横媚，于是被迫代替她去往敵國和親纠炮。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 45,876評論 2贊 361