只用于個人的學習和總結，
178. Rank Scores
一、表資訊
二、題目資訊
對上表中的成績由高到低排序，并列出排名，當兩個人獲得相同分數時，取并列名次，且名詞中無斷檔，
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.
For example, given the above Scores table, your query should generate the following report (order by highest score):
三、參考SQL
（1）方法一：直接表內連接
1 select s1.Score,count(distinct s2.Score) as 'Rank' 2 from Scores s1 3 inner join Scores s2 4 on s1.Score<=s2.Score 5 group by s1.id 6 order by count(distinct s2.Score);
分組欄位和查詢欄位不一致，可以在嵌套一層select，
解題思路：
1、欲得到排名，肯定用count進行統計，一個表肯定不行；
2、連接條件：得到大于或等于某個數的集合，比如大于等于3.50的集合就是{3.50，3.65，4.00，3.85，4.00，3.65}
3、分組：得到大于或等于某個數的6個集合組
4、去重統計：因為是排名無斷檔，需要進行去重再統計，不然就變成統計集合的個數（即大于等于某個值的個數），而不是該值在集合中排名
（2）方法二：視窗函式——dense_rank()（MySQL8.0）
1 SELECT Score, 2 DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank' 3 FROM Scores;
視窗函式復習：https://zhuanlan.zhihu.com/p/135119865
180. Consecutive Numbers
一、表資訊
二、題目資訊
找出連續出現三次及以上的數字，例如，上表中，應該回傳數字 1，
Write a SQL query to find all numbers that appear at least three times consecutively. For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
三、參考SQL
方法一：多次連接
select distinct a.num as ConsecutiveNums from Logs a inner join Logs b on a.id=b.id+1 inner join Logs c on a.id=c.id+2 where a.num=b.num and a.num=c.num;
思路總結：
1.連續三次出現，意味著ID連續、值相等，
2.多次連接時，讓當前記錄、下條記錄、下下條記錄拼接在一起
3.篩選值相等的行記錄，有可能連續出現大于3次，去重即可得到該num，
方法二：視窗函式——行向下偏移lead()
select distinct Num as ConsecutiveNums from (select Num, lead(num,1) over(order by id) as next_num, lead(num,2) over(order by id) as next_next_num from Logs) t where t.Num=t.next_num and t.Num=t.next_next_num;
視窗函式lead()：https://www.begtut.com/mysql/mysql-lead-function.html
181. Employees Earning More Than Their Managers[e]
一、表資訊
如下 Employee 表中包含全部的員工以及其對應的經理，
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
二、題目資訊
基于如上 Employee 表，查出薪水比其經理薪水高的員工姓名，
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
三、參考SQL
自連接：
1 select e1.Name as Employee 2 from Employee e1 3 inner join Employee e2 4 on e1.ManagerId=e2.Id 5 where e1.Salary>e2.Salary;
182. Duplicate Emails[e]
一、表資訊
二、題目資訊
查詢重復的郵箱
Write a SQL query to find all duplicate emails in a table named Person. For example, your query should return the following for the above table:
三、參考SQL
方法一：自己寫的
select Email from Person group by Email having count(*)>1;
方法二：官方答案
1 SELECT Email FROM 2 (SELECT Email, COUNT(id) AS num 3 FROM Person 4 GROUP BY Email) AS tmp 5 WHERE num > 1;
183. Customers Who Never Order[e]
一、表資訊
假設一個網站上包含如下兩張表：顧客表和訂單表
Suppose that a website contains two tables, the Customers table and the Orders table.
表一：Customers
表二：Orders
二、題目資訊
找出沒有下過訂單的顧客姓名，
Write a SQL query to find all customers who never order anything. Using the above tables as an example, return the following:
三、參考SQL
方法一：左外連接
1 select Name as Customers 2 from Customers c 3 left join Orders o 4 on c.Id=o.CustomerId 5 where o.Id is null;
方法二：子查詢（官方方法）
select Name as Customers from Customers where Id not in( select CustomerId from Orders );
184. Department Highest Salary[M]
一、表資訊
表一：Employee
表二：Department
二、題目資訊
查詢每個部門中，薪水最高的員工姓名及其薪水，
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).
三、參考SQL
方法一：視窗函式——dense_rank()
select d.Name as Department,t.Name as Employee,t.Salary from ( select *, dense_rank() over(partition by DepartmentId order by Salary DESC) as ranking from Employee ) t inner join Department d on t.DepartmentId=d.Id where ranking=1;
同一層select下，欄位別名不用能與條件篩選！！！執行順序問題from——.....——where——.....——select——.....
思路：
1.用dense_rank()不斷檔的方式，給各個部門分組的工資大小排名
2.取排名為1的都是最大工資
方法二：關聯子查詢
1 select 2 d.Name as Department, 3 t.Name as Employee, 4 t.Salary 5 from Department d 6 inner join 7 (select Name,DepartmentId,Salary 8 from Employee e 9 where (e.DepartmentId,Salary) in 10 (select DepartmentId,max(Salary) 11 from Employee 12 group by DepartmentId) 13 ) t 14 on d.Id=t.DepartmentId;
in可以進行多屬性值（column1_name, column2_name,....）進行篩選，一一對應所篩選的欄位，
思路：
1.找出部門中最大的工資
2.讓原始表中各部門的工資等于最大工資，羅列出所有最大工資，
3.內連接查詢相關資訊
185. Department Top Three Salaries[h]
經典topN問題：記錄每組最大的N條記錄，既要分組，又要排序，
一、表資訊
表一：Employee
表二：Department
二、題目資訊
查詢各部門薪水排名前三名的員工姓名及薪水，
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).
三、參考SQL
方法一：視窗函式——dense_rank()
1 select 2 d.name as Department, 3 e.name as Employee, 4 Salary 5 from Department d 6 inner join 7 ( 8 select name,Salary,DepartmentId, 9 dense_rank() over(partition by DepartmentId order by Salary desc) as ranking 10 from Employee 11 ) e 12 on d.Id=e.DepartmentId 13 where ranking<=3;
思路：
1.用dense_rank()，按照部門分組并降序排列，不間斷編上排名
2.篩選排名小于等于3的記錄，就是前三工資的記錄，
（ps:用視窗函式做，思路和上題差不多，區別只是后面篩選的條數）
方法二：自連接分組篩選
1 select 2 d.name as Department, 3 e.name as Employee, 4 Salary 5 from Department d 6 inner join 7 ( 8 select e1.Id,e1.DepartmentId,e1.name,e1.Salary 9 from Employee e1 10 inner join Employee e2 11 on e1.DepartmentId=e2.DepartmentId 12 and e1.Salary<=e2.Salary 13 group by e1.Id 14 having count(distinct e2.Salary)<=3 15 ) e 16 on d.Id=e.DepartmentId;
思路：
1.關鍵是要找出各部門前三工資的記錄：
自連接，連接條件為部門相等，工資比我大或者相等；
按員工分組，則組記錄為比我大或者相等全部員工記錄；
統計組記錄條數，少于等于3條，則表示我一定是工資第三的，這里有一點注意，不能用count(*)，因為和我工資相等的員工除了我本身，還有可能有其他員工，如果不去重，就會導致記錄條數大于3（假設我剛好是第三）,從而篩選掉，這不是想要的結果;
2.再按需求做相關查詢即可
（ps這題的自連接條件思路和178題差不多）
196. Delete Duplicate Emails[E]
一、表資訊
二、題目資訊
洗掉郵件重復的行，當有重復值時，保留Id最小的行，
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id. For example, after running your query, the above Person table should have the following rows:
三、參考SQL
方法一：子查詢
1 delete from Person 2 where Id not in( 3 select Id from 4 ( 5 select min(Id) AS Id 6 from Person 7 group by Email 8 ) t 9 );
思路：
1.子查詢找出不用洗掉的郵箱Id集合（重復郵箱的最小Id加上郵箱不重復的Id）：郵箱分組，取最小Id即可，
2.洗掉時，判斷Id不在此集合即可
（ps：MySQL不讓同時對統一表進行修改和查詢操作，所以需要外層嵌套一層輔助表；min(Id)要記得起別名）
方法二：自連接
1 delete p1 from Person p1 2 inner join Person p2 3 on p1.Email=p2.Email 4 and p1.Id>p2.Id;
思路：
1.郵箱相等進行連接得到的集合為：（1）郵箱相等，Id也相等，即不重復的（2）郵箱相等，p1.Id>p2.Id（3）郵箱相等，p1.Id<p2.Id
2.把郵箱相等，p1.Id>p2.Id提取出來，洗掉端即可，這樣就保留了小和不重復的，
（ps：聯級洗掉也有這種語法——delete 表名 from .....）
197. Rising Temperature[E]
一、表資訊
二、題目資訊
以下圖為例，找出比前一天溫度高的id，
Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).
Return the result table in any order.
The query result format is in the following example:
三、參考SQL
1 select w1.id as Id from Weather w1 2 inner join Weather w2 3 on datediff(w1.recordDate,w2.recordDate)=1 4 where w1.temperature>w2.temperature;
思路：
1.自內連的笛卡爾積中，去取出間隔相差一天的記錄，用datadiff()函式，
2.再篩選出溫度比上一天高ID即可，
（ps;日期不能進行簡單的相加相減，最好使用日期函式，https://www.w3school.com.cn/sql/sql_dates.asp）
復習日期函式：https://leetcode-cn.com/problems/rising-temperature/solution/tu-jie-sqlmian-shi-ti-ru-he-bi-jiao-ri-qi-shu-ju-b/
262. Trips and Users[H]
一、表資訊
表一：Trips
該表包含全部出租車資訊的記錄，每一條記錄有一個 Id，ClientId 和 Drive_Id 都是與 Users 表聯結的外鍵，Status 包含 completed, cancelled_by_driver, 和 cancelled_by_client 三種狀態，
The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
表二：Users
該表包含全部的用戶資訊，每一個用戶都有一個 Id，Role有三種狀態：client, driver 以及 partner，
The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
二、題目資訊
找出2013年10月1日至2013年10月3日期間，每一天 未被禁止的 (unbanned) 用戶的訂單取消率，
Write a SQL query to find the cancellation rate of requests made by unbanned users (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.
取消率的計算方式如下：(被司機或乘客取消的非禁止用戶生成的訂單數量) / (非禁止用戶生成的訂單總數)
For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
三、參考SQL
方法一：子查詢篩選出有效訂單記錄
SELECT Request_at AS 'Day', round( count( CASE t_Status WHEN 'completed' THEN NULL ELSE 1 END ) / count( * ), 2 ) AS 'Cancellation Rate' FROM ( SELECT Request_at, Status as t_Status FROM Trips WHERE Client_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned = 'Yes' ) AND Driver_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned = 'Yes' ) ) t WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at
思路：
1.重要一點是篩選出有效訂單記錄集合：顧客和司機都是未被禁止的！
子查詢：
Client_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned ='Yes' )
AND Driver_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned = 'Yes')
2.在上一步基礎上，統計每天分組被取消的訂單，用case when陳述句：當訂單是complated完成狀態時，回傳null，這樣count就不會計數，
方法二：連接查詢篩選出有效訂單記錄集合
https://leetcode-cn.com/problems/trips-and-users/solution/san-chong-jie-fa-cong-nan-dao-yi-zong-you-gua-he-n/
計算訂單取消率還可以用avg(Status!='completed')：
https://leetcode-cn.com/problems/trips-and-users/solution/ci-ti-bu-nan-wei-fu-za-er-by-luanz/
511. Game Play Analysis I[E]
一、表資訊
該Activity表記錄了游戲用戶的行為資訊，主鍵為(player_id, event_date)的組合，每一行記錄每個游戲用戶登錄情況以及玩的游戲數(玩的游戲可能是0)，
(player_id, event_date) is the primary key of this table. This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.
二、題目資訊
查詢每個用戶首次登陸的日期
Write an SQL query that reports the first login date for each player.
The query result format is in the following example:
三、參考SQL
1 SELECT 2 player_id, 3 MIN( event_date ) AS first_login 4 FROM 5 Activity 6 GROUP BY 7 play_id 8 ORDER BY 9 play_id;
512. Game Play Analysis II[E]
一、表資訊
同上題
二、題目資訊
查詢每個用戶首次登陸的日期所使用的設備，
Write a SQL query that reports the device that is first logged in for each player.
三、參考SQL
方法一：內連接+子查詢
1 SELECT 2 a.player_id, 3 a.device_id 4 FROM 5 Activity AS a 6 INNER JOIN ( SELECT player_id, MIN( event_date ) AS first_login FROM Activity GROUP BY player_id ORDER BY player_id ) AS b 7 ON a.player_id = b.player_id AND a.event_date = b.first_login 8 ORDER BY 9 a.player_id;
思路：
1.通過子查詢查出表b：每個玩家最早登錄的日期
2.再進行內連接（或者where篩選都可以），
方法二：視窗函式dense_rank()
1 SELECT 2 player_id, 3 device_id 4 FROM 5 ( SELECT player_id, device_id, RANK ( ) OVER ( PARTITION BY player_id ORDER BY event_date ) AS rnk FROM Activity ) AS tmp 6 WHERE 7 rnk = 1;
思路：
1.player_id分組，event_date升序，不間斷排名
2.取排名為1即為玩家最早登錄的資訊記錄
534. Game Play Analysis III[M]——分組累加和
一、表資訊
同上
二、題目資訊
按照日期，查詢每個用戶到目前為止累積玩的游戲數，
Write an SQL query that reports for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date. Check the example for clarity.
三、參考SQL
方法一：視窗函式sum()
1 SELECT 2 player_id, 3 event_date, 4 SUM( games_played ) over ( PARTITION BY player_id ORDER BY event_date ) AS games_played_so_far 5 FROM 6 activity;
思路：
1.player_id分組，event_date升序
2.對分組后的games_played進行累計求和
方法二：內連接后分組統計
1 SELECT 2 a1.player_id, 3 a1.event_date, 4 SUM( a2.games_played ) AS games_played_so_far 5 FROM 6 activity a1 7 INNER JOIN activity a2 ON a1.event_date >= a2.event_date 8 AND a1.player_id = a2.player_id 9 GROUP BY 10 a1.player_id, 11 a1.event_date;
思路：
1.對于這種需要分組累計統計的（求和、計數也好），內連接的連接條件一般都是非等值連接，讓主表的某個欄位的值對應連接從表的同樣欄位的多個值
這樣對主表的該欄位進行分組后，就可以對從表的某個欄位進行統計操作，
2.涉及到需要分組兩次的話，還要注意連接條件要加多一個等值判斷，避免組內的欄位的值連接到其他組的欄位值
沒有進行組內的等值連接條件的限定，不同組的值亂連接匹配，導致最后分組統計結果不正確，
3.得到連接總表后，按要求進行統計即可，
550. Game Play Analysis IV[M]
一、表資訊
同上題
二、題目資訊
查詢首次登錄后第二天也登錄的用戶比例，
Write an SQL query that reports the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.
The query result format is in the following example:
三、參考SQL
方法一：內連接（統計連續兩天登錄）
1 SELECT 2 ROUND( 3 COUNT( CASE datediff( a1.event_date, a2.event_date ) WHEN 1 THEN 1 ELSE NULL END ) / COUNT(DISTINCT a1.player_id),2) AS fraction 4 FROM 5 activity a1 6 INNER JOIN activity a2 7 ON a1.player_id = a2.player_id 8 AND a1.event_date >= a2.event_date;
思路：
1.內連接的條件和思路和上題一樣
2.統計連續兩天登入，只需要同一用戶，登錄日期相差一天即可，（注意：這里不是統計首次登錄第二天也登錄的記錄，而是只要連續兩天登錄的記錄，因為開始日期可能不是最小日期）
方法二：子查詢+外連接（統計統計首次登錄第二天也登錄）
SELECT ROUND( COUNT( DISTINCT t.player_id ) / COUNT( DISTINCT a1.player_id ), 2 ) AS fraction FROM activity a1 LEFT JOIN ( SELECT player_id, MIN( event_date ) AS first_login FROM activity GROUP BY player_id ) t ON a1.player_id = t.player_id AND DATEDIFF( a1.event_date, t.first_login ) = 1;
思路：
1.先用子查詢查出每個用戶登錄的最早時間first_login
2.左外連接：id相等，和最早登錄時間相差一天，得到的表為：第二天用戶也登錄的記錄會和first_login連接，第二天不登錄用戶irst_login則為null（不是相差一天）
3.統計第二天登錄的用戶：COUNT( DISTINCT t.player_id )，null值不統計；不能用COUNT( t.player_id is not null )
（PS：原則上 t.player_id記錄是唯一的，除非一個用戶第二天登錄會產生多條記錄，而不是記錄最后一次登錄）
方法三：視窗函式_FIRST_VALUE()
1 SELECT 2 ROUND(COUNT(DISTINCT t.player_id)/COUNT(DISTINCT a1.player_id),2) AS fraction 3 FROM activity a1 4 LEFT JOIN (SELECT player_id,first_value(event_date) over(partition by player_id ORDER BY event_date) AS first_login FROM activity) t 5 ON a1.player_id=t.player_id 6 AND DATEDIFF(a1.event_date,t.first_login)=1
569. Median Employee Salary[H]
一、表資訊
下面的員工表包含全部的員工ID，公司名稱以及每個員工的薪水，
The Employee Table holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary.
二、題目資訊
找出各公司薪水的中位數，不用SQL內建函式，
Write a SQL query to find the median salary of each company. Bonus points if you can solve it without using any built-in SQL functions.
三、參考SQL
方法一：根據中位數最原始的定義
1 SELECT 2 e1.Id, 3 e1.company, 4 e1.salary 5 FROM 6 (SELECT Id,company,salary,@rnk:=IF(@pre=company, @rnk:=@rnk+1,1) AS rnk,@pre:=company 7 FROM employee,(SELECT @rnk:=0, @pre:=NULL) AS init 8 ORDER BY company,salary,Id 9 ) e1 10 INNER JOIN 11 (SELECT company,COUNT(*) AS cnt FROM employee GROUP BY Company 12 ) e2 13 ON e1.company=e2.company 14 WHERE e1.rnk IN (cnt/2+0.5,cnt/2,cnt/2+1);
思路：
中位數定義：奇數個數字時，中位數是中間的數字；偶數個數字時，中位數中間兩個數的均值（這里只列出兩個數，不求值），即，數列總個數為N，則：
-
N為奇數，中位數排序編號是(N+1)/2=N/2+0.5
-
N為偶數，中位數排序編號是N/2和N/2+1
由于一個數列N總個數不是奇就是偶（互斥），所以（N/2+0.5）和（N/2、N/2+1）也是互斥，兩個元組的元素不可能同時為整數，也就是說無論數列總個數N是奇還偶，都可以直接這樣判斷：
中位數位置序號 IN （N/2+0.5，N/2，N/2+1）
基于上述可以得到大致的思路：
1.對薪水進行分組排序（不間斷連續），用自定義變數方法或者MySQL8.0的ROW_NUMBER()視窗函式
2.求總個數cnt，count（*）
3.篩選出中位數的位置序號e1.rnk IN (cnt/2+0.5,cnt/2,cnt/2+1)
其他方法：
https://www.cnblogs.com/qcyye/p/13451067.html
https://zhuanlan.zhihu.com/p/257081415
570. Managers with at Least 5 Direct Reports[M]
一、表資訊
下面員工表中包含各部門員工資訊及其對應的經理，
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
二、題目資訊
查詢出至少管理5個員工的經理的名稱，
Given the Employee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:
三、參考SQL
方法一：子查詢
1 SELECT NAME 2 FROM 3 employee 4 WHERE 5 Manager_id IN ( SELECT Manager_id FROM employee GROUP BY Manger_id HAVING COUNT( * ) >= 5 );
571. Find Median Given Frequency of Numbers[H]
一、表資訊
下表記錄了每個數字及其出現的頻率，
The Numbers table keeps the value of number and its frequency.
二、題目資訊
根據每個數字出現的頻率找出中位數，
Write a query to find the median of all numbers and name the result as median. In this table, the numbers are 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3, so the median is (0 + 0)/2 = 0.
三、參考SQL
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/221603.html
標籤：MySQL