leetcode力扣データベースSQLテーマと解答(三)

今日の目標はやはり難題と簡単な問題の組み合わせで拳を出して、引き継ぎました!くだらないことは言わないで、始めましょう.
文書ディレクトリ

185. 部門給与上位3社全員(困難)

構想1:自己接続

構想2:開窓関数

196.重複するメールボックスを削除(単純)

考え方1:マルチテーブル削除

考え方2:削除+NOT IN

182.重複するメールボックスの検索(簡単)

181.マネージャの収入を超える従業員(単純)

183.注文しないお客様(単純)

構想1:not in

構想2:is null

185.部門給与の上位3社のすべての従業員(困難)
経典のTOP N問題の解法;難易度が少し高くなれば、グループ内のトップNを獲得することもできますよ.超定番タイトル!!!
Employeeテーブルには、すべての従業員情報が含まれています.各従業員には、対応する労働番号Id、名前Name、給与Salary、部門番号DepartmentIdがあります.
Id
Name
Salary
DepartmentId
1
Joe
85000
1
2
Henry
80000
2
3
Sam
60000
2
4
Max
90000
1
5
Janet
69000
1
6
Randy
85000
1
7
Will
70000
1
Departmentテーブルには、会社のすべての部門の情報が含まれています.
Id
Name
1
IT
2
Sales
SQLクエリーを作成し、各部門で上位3位の給料をもらったすべての従業員を見つけます.たとえば、上記で指定したテーブルに基づいて、クエリの結果は次のように返されます.
Department
Employee
Salary
IT
Max
90000
IT
Randy
85000
IT
Joe
85000
IT
Will
70000
Sales
Henry
80000
Sales
Sam
60000
説明:
IT部門ではMaxが最高賃金を獲得し、RandyもJoeも2番目に高い賃金を獲得し、Willの賃金は3位だった.販売部門(Sales)には従業員が2人しかいないが、Henryの給料が最も高く、Samの給料は2位だ.
問題:
構想1:自己接続
このようなパケット内で上位N名を取る問題については、groupbyを先にグループ化し、having count()でフィルタリングすることができる.例えば、この問題では、各部門の給料の上位3位を探して、サブクエリでEmployeeで自分と接続し、接続条件は「部門は同じだが給料は私より高い」で、次にhaving count(Salary)<=3に従って選別する原理は、「私と同じ部門で給料が私より高い人数」が3つを超えなければ、私は部門の給料の上位3位に違いない.これにより、内層クエリーでは、要件に合致するすべての従業員IDをクエリーでき、その後、外層クエリーが簡単になります.

select de.Name as Department,t.Name as Employee, t.Salary as Salary
from
(
    select e1.Name,e1.Salary,e1.DepartmentId
    from Employee e1 left join Employee e2
    on e1.DepartmentId = e2.DepartmentId
    and e1.Salary <= e2.Salary
    group by e1.Name
    having count(distinct e2.Salary)<=3
    order by e1.Salary desc
) as t
join Department as de
on t.DepartmentId = de.Id
order by de.Name asc,t.Salary desc

エラーポイント:

グループ:必ずグループbyを加えなければなりません.このテーマの要求は各部門のTOP 3を得ることですから、グループがなければ、最終的な結果は全体のTOP 3ではないでしょうか.

順序:私は自己反省して、意外にも順序を書き間違えて、正しい順序はgroup by-having-order by~であるべきですが、私はこの3つのキーワードの順序を覚える良い方法を考えました:byの両側を歩いて、havingは真ん中にあります.ほら、対称ですね.次は絶対に間違いを犯しません.

カウント:表2の給与>表1の給与であるため、表1の各行のレコードについては、表2のいずれにおいてもN行が表1より大きいレコードが存在するので、ここではランキング

を得るためにcount(表2)すべきである.
考え方2:開窓関数
開窓関数を使用してランキングを取得します(SqlServer、Orcalなど開窓関数をサポートするデータベースにのみ適用されます.MySQL環境では開窓関数は使用できません).

select de.Name as Department,t.Name as Employee,t.Salary as Salary
from 
(
    select Name,Salary,DepartmentId,
    dense_rank() over(partition by DepartmentId order by Salary desc) as rk
    from Employee
) t
join Department de 
on t.DepartmentId = de.Id
where t.rk<=3

196.重複するメールボックスを削除する(簡単)
題名:deleteの使い方を考察して1つのテーブルを削除したり、複数のテーブルを削除したりする場合
考え方1:マルチテーブル削除
eg 1:どのテーブルを削除するかを指定する必要がないテーブルがあります–DELETE FROM person WHERE id=3 eg 2:どのテーブルを削除するかを指定するテーブルが2つあります

delete  p1 
from Person p1 join Person p2 
on p1.email = p2.email and p1.id>p2.id

考え方2:削除+NOT IN
まずemailに基づいてグループ化し、Idの最小保存を見つけます.テーブルを削除するときはIdを上記の範囲内にしないようにするだけです

delete from Person
where Id not in
(select t.id from
#            ，  mysql    select              
# You can't specify target table 'Person' for update in FROM clause
    (select min(Id) as id from Person
    group by Email
    ) AS t
)

182.重複するメールボックスの検索(簡単)
SQLクエリーを作成し、Personテーブルの重複するすべての電子メールボックスを検索します.
标题:考察group by+having(パケット後フィルタリング)メールボックスに従ってパケット化し、パケット化後havingを用いて個数>=2のメールボックスをフィルタリングする

select Email from Person
group by Email
having count(Email)>=2 

181.マネージャーの収入を超えた従業員(簡単)
Employeeテーブルには、すべての従業員が含まれており、マネージャも従業員に属しています.各従業員にはIdがあり、従業員に対応するマネージャのIdの列もあります.
Employeeテーブルを指定して、SQLクエリーを作成します.このクエリーは、マネージャよりも収益の高い従業員の名前を取得できます.上の表では、Joeはマネージャーを上回る収入を持つ唯一の従業員です.問題解:考察自己接続はjoin自己接続を用い,e 1は従業員表,e 2は対応する部門主管表である.それから表1の給料>表2の給料でいいです.

select e1.Name AS Employee
from Employee as e1
join Employee as e2 #   join  
on e2.Id = e1.ManagerId
where e1.Salary > e2.Salary 

183.注文しないお客様(簡単)
Webサイトには、2つのテーブル、Customersテーブル、Ordersテーブルがあります.SQLクエリーを作成し、何も注文していないすべてのお客様を見つけます.
考え方1:not in
まず注文表に購入記録のあるユーザーIdを見つけて、それからすべてのユーザーの中で選別して、これらの購入した商品のユーザーを排除して、残りは買い物をしたことがないユーザーです!

select Name as Customers 
from Customers where Id not in
(select CustomerId from Orders)

考え方2:is null
お客様が消費記録を持っている場合は、必ずOrdersテーブルに一致します.そうでない場合は、Ordersテーブル対応はnullとなります.これが左接続です.素晴らしいですね.

select c.Name as Customers 
from Customers  c
left join Orders   o
on c.Id = o.CustomerId 
where o.CustomerId is null

エラーポイント:nullフィールドかどうかを判断するには、等しい番号ではなくis nullまたはis not nullを使用します.