Leetcode-MYSQL特集

Leetcode-MYSQL特集
少し時間をかけてLeetcodeのSQL部分の非有料問題を作ってみましたが、多くの例題は古典的です~すべての複雑な論理は1つの文の中で完成することができます.select文の奥深さを実感しました.主に関連する点は、select別名、selectマルチテーブル、join、case、distinct、count()、where、group by、order by、if、IsNull、日付関連関数です.select関数の完全な構文は次のとおりです.

select [ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]

 
175.Combine Two Table題意:2枚の表を合併して、1枚目の表の行を全部要求して、2枚目の表は放っておいて、だからLEFT JOINを採用する.リンク:https://leetcode.com/problems/combine-two-tables/問題解:left joinを使用してテーブルの左外接続を行います.

select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;

 
 176.Second Highest Salary題意:2番目に大きい項目を出力し、存在しない場合nullを返す.リンク:https://leetcode.com/problems/second-highest-salary/問題解:異なるデータはdistinctキーワードを使用する.ソートはorder byを採用します.逆シーケンス用desc;Limitは開始と個数を限定するために使用される.次にasキーでテーブルの別名を定義します.caseとwhenキーワードは分岐判断を行い,ここで主にtrickが存在しない場合nullを返す必要がある点がある.

select 
    case c.cnt
    when 0 then
        null
    else
        c.Salary
    end as SecondHighestSalary
from
    (select count(Salary) as cnt, Salary 
     from 
        (select distinct Salary 
         from Employee 
         order by Salary desc limit 1, 1
        ) as b
    ) as c

 
  177.Nth Highest Salary題意:N番目に大きい項目を出力し、存在しない場合nullを返す.リンク:https://leetcode.com/problems/nth-highest-salary/問題解:176と似ていますが、limitのパラメータは式ではないので、Nは予め1つ減らす必要があります.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N - 1;
  RETURN (
        select 
            case c.cnt
            when 0 then
                null
            else
                c.Salary
            end as xx
        from
            (select count(Salary) as cnt, Salary 
             from 
                (select distinct Salary 
                 from Employee 
                 order by Salary desc limit N, 1
                ) as b
            ) as c
  );
END

 
  178.Rank Scores題意:Score降順で並べ替えられた表と順位を出力し、同じ点数で順位が一致し、順位は連続整数である.リンク:https://leetcode.com/problems/rank-scores/題解:考察したのは2つのselectネストである:外層select表aは直接点数降順に並べ替えられる;内層select表bは表a中のscore値より大きい異なる要素の個数を統計し,さらに1を加えるとRank値である.

select Score, 
    (select count(distinct Score) 
     from Scores as b 
     where a.score < b.score
    ) + 1  as Rank
from Scores as a order by Rank;

 
180.Consecutive Number題意:出力テーブルの少なくとも3つの隣接テーブル項目の数字が同じ数である.リンク:https://leetcode.com/problems/consecutive-numbers/問題解:考察するのはテクニックです.1回のselectで同じテーブルを複数枚作成し、隣接する2つの判定等(下付きの差1、Num項が等しい)により条件を満たすすべての三元群(x,y,z)をフィルタリングすることができる.ここでx=y=zであるから、そのうちの1列を任意に統計してdistinctで判定再フィルタ出力を行えばよい.

select distinct x as ConsecutiveNums
from
    (select l1.Num as x, l2.Num as y, l3.Num as z
    from Logs l1,
         Logs l2,
         Logs l3
    where l1.Id = l2.Id - 1
        AND l2.Id = l3.Id - 1
        AND l1.Num = l2.Num
        AND l2.Num = l3.Num
     ) as a

 
181.Employees Earning More Than Their Management題意:従業員の給料と彼の直接上司を与え、従業員の中で「給料が自分の直接上司より高い」従業員の名前を求める.リンク:https://leetcode.com/problems/employees-earning-more-than-their-managers/标题:180の簡略版.1行selectの2枚の表e 1とe 2、e 1の直接上司番号はe 2の番号に等しく、e 1の給料はe 2の給料より大きく、すべてのe 1を統計する.Nameでいいです.

select e1.Name as Employee
from Employee e1, 
     Employee e2 
where e1.ManagerId = e2.Id
    AND e1.Salary > e2.Salary;

 
182.Duplicate Emails題意:重複するすべてのメールを出力する;リンク:https://leetcode.com/problems/duplicate-emails/問題解:groupbyを考察し,Emailをグループ化しcount(colname)で統計する.

select Email
from
    (select Id, Email, count(Email) as e 
     from Person group by Email
    ) as x
where x.e > 1;

 
183.Customers Who Never Order題意:注文していないすべての顧客名を出力する;リンク:https://leetcode.com/problems/customers-who-never-order/問題解:多層selectのネストとnot inの使用を考察する.お客様によっては同名の可能性があります.

select c.Name as Customers 
from Customers as c
where c.Id not in 
    (select n.CustomerId
     from 
        (select CustomerId, c.Id, Name from 
            Orders o,
            Customers c
         where c.Id = CustomerId
        ) as n
    )

 
184.Department Highest Salary題意:各部門で最も給料の高い人と給料情報を出力する.リンク:https://leetcode.com/problems/department-highest-salary/問題解:多層selectのネストとgroupbyパケットの応用を考察する.

select d.Name as Department, c.Employee, c.Salary 
from
    (select a.DepartmentId, a.Name as Employee, a.Salary as Salary
     from
        Employee as a,
        (select DepartmentId, max(Salary) as Salary 
         from Employee 
         group by DepartmentId
        ) as b
     where 
             a.Salary = b.Salary
        and  a.DepartmentId = b.DepartmentId
    ) as c,
    Department as d
where d.Id = c.DepartmentId

 
185.Department Top Three Salaries題意:各部門の給料の上位3つを探して出力する.リンク:https://leetcode.com/problems/department-top-three-salaries/まず、給料の上位3人の特徴は、自分の給料とそれより高い(重い)人数が3以下であることです.この特徴によれば、これらの条件を満たすすべての人を2つのネストされたselectにgroupbyとcountを加えてIdを見つけることができる.そして、これらのIdに基づいて部門、給与情報を見つけます.

select d.Name as Department, h.Employee, h.Salary
from
   (select g.DepartmentId, g.Name as Employee, g.Salary
    from
    (
        select a.Id
        from
           (select e1.Id, count(distinct e2.Salary) as cnt
            from
                Employee e1,
                Employee e2
            where e1.DepartmentId = e2.DepartmentId
                and (
                    e1.Salary <= e2.Salary
                )
            group by e1.Id
           ) as a
        where cnt <= 3
    ) as b
    left join Employee g
    on b.Id = g.Id
   ) as h
left join Department d
on h.DepartmentId = d.Id
where not IsNull(d.Name)
order by h.DepartmentId, h.Salary desc
;

 
 
196.Delete Duplicate Emails題意:重複メールを削除し、番号の小さいものを保留する.リンク:https://leetcode.com/problems/delete-duplicate-emails/标题:delete from where....where文でいくつかの判断を行い、右ボタンをgroupbyで分類して最小のIdを取ってリストを構成し、削除するときにリストにあるかどうかを判断します.

delete 
from Person
where Id not in 
(
    select *
    from
    (select min(Id) as Id
     from Person 
     group by Email
    ) as x
);

 
197.Rising Temperature題意:毎日の温度を与え、前日より温度が高い日の番号をすべて出力する.リンク:https://leetcode.com/problems/rising-temperature/題解:多対日付操作関数の運用を考察する.a.RecordDate in(select interval 1 day+b.RecordDate)は、a.RecordDateがb.RecordDateであるか否かを判断する次の日として用いることができる.

select a.Id
from Weather a,
     Weather b
where a.RecordDate in (select interval 1 day + b.RecordDate)
    and a.Temperature > b.Temperature;

 
262.Trips and Users題意:滴滴タクシー旅程表、乗客表を与え、2013-10-01から2013-10-03号までの有効乗客を出力したのはタクシーキャンセル率で、小数点以下2位まで正確である.リンク:https://leetcode.com/problems/trips-and-users/題名:まずTripsとUsersでleft joinを利用してnoBannedTrips表を1枚スクリーニングしてすべての人が合法的な旅程表であることを代表して、同じ方法ですべての旅程が完成した合法的な旅程表noBannedAndNotCompeedTripsを得る.そして、この2枚の表を時間通りにgroupbyとcount(*)して時間メインキーの旅程表allTripsとキャンセル旅程表allCancellTripsを得、この2枚の表を時間でleft joinして計算してroundで四捨五入し、最後に時間制限を加えることを忘れないでください.

select Day, round( if(IsNull(up),0,up) / if(IsNull(dwn),1,dwn), 2 ) as 'Cancellation Rate'
from
(
    select allTrips.Request_at as Day, allTrips.cnt as dwn,  allCancellTrips.cnt as up
    from
        (
         select Request_at, count(*) as cnt
         from 
            (
                select ta.Id, ta.Client_Id, ta.Request_at
                from Trips ta
                left join Users ua
                on ta.Client_Id = ua.Users_Id
                where ua.Banned = "No"
            ) as noBannedTrips
         group by Request_at
        ) as allTrips
    left join
        (
         select Request_at, count(*) as cnt
         from 
            (
                select ta.Id, ta.Client_Id, ta.Request_at
                from Trips ta
                left join Users ua
                on ta.Client_Id = ua.Users_Id
                where ua.Banned = "No"
                and ta.Status != "completed"
            ) as noBannedAndNotComletedTrips
         group by Request_at
        ) as allCancellTrips
    on allTrips.Request_at = allCancellTrips.Request_at
) as result
where result.Day >= '2013-10-01'
    and result.Day <= '2013-10-03';

 
595.Big Countries題意:すべての人口が250000000以上、または面積が300000以上の国を輸出する.リンク:https://leetcode.com/problems/big-countries/标解:考察条件または文where...or ....

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

 
596.Classes More Than 5 Students題意:与えられた課程と授業の学生は、学生数が4より大きい授業の名前を出力することを要求する.リンク:https://leetcode.com/problems/classes-more-than-5-students/問題解:groupbyが二重キーワードに従って再利用し、groupbyを利用して分類した後にcountで統計する使い方を考察する.

select class
from
    (select class, count(class) as cc
     from 
        (select student, class
         from courses group by class, student
        ) as unique_courses
     group by class
    ) as b
where b.cc > 4;

 
601.Human Traffic of Stadium題意:連続3つの記録の中で人数が百を超えた日数を出力する.リンク:https://leetcode.com/problems/human-traffic-of-stadium/問題解:一度にselectの3つの同じテーブルを考察し、idに従ってそれぞれ比較し、条件を満たすすべての記録idをフィルタし、left joinで元のテーブルに戻ってデータクエリーを行う.

select t.id, t.date, t.people
from
   (
    select distinct 
        s1.id 
    from 
        stadium s1,
        stadium s2,
        stadium s3
    where   (s1.id + 1 = s2.id
        and s2.id + 1 = s3.id
        and s1.people >= 100
        and s2.people >= 100
        and s3.people >= 100)
       or (s1.id - 1 = s2.id
        and s2.id - 1 = s3.id
        and s1.people >= 100
        and s2.people >= 100
        and s3.people >= 100)
       or (s1.id + 1 = s3.id
        and s1.id - 1 = s2.id
        and s1.people >= 100
        and s2.people >= 100
        and s3.people >= 100)
) as a
left join stadium t
on a.id = t.id;

 
620.Not Boring Movies題意:1枚の表を与え、条件によって選別する;リンク:https://leetcode.com/problems/not-boring-movies/問題解:%は型を取るために使用されます;where andは条件文に使用されます.order byでソートします.

select *
from cinema
where id % 2 = 1
    and description != "boring"
order by rating desc;

 
626.Exchange Seats題意:奇数と偶数の学生が名前を交換する.リンク:https://leetcode.com/problems/exchange-seats/問題解:if文の使用を考察する.

select a.id, if( a.id in (select count(*) from seat) and a.id%2=1, a.student, b.student ) as student
from
    (
     select id, student, (FLOOR((id+1)/2)*2-(1-id%2)) as next
     from seat
    ) as a
left join seat b
on a.next = b.id
order by a.id;

 
627.Swap Salary題意:表のsexフィールドの男女を交換する;リンク:https://leetcode.com/problems/swap-salary/問題解:if文の使用を考察する:if(条件、条件は実行文を満たし、条件は実行文を満たさない).

update salary 
set sex=if(salary.sex = "f", "m", "f");