瀏覽標籤：

LeetCode

[MySQL][LeetCode][Medium] 180. Consecutive Numbers

心得

這題要找出連續三次重複出現的數字，我原本一直在想如果中間有斷層(中間有資料被刪除)的話是否要先自己排序一次，結果看了一下Top Solutions才發現根本不用考慮這個問題，直接ID+1尋找下筆資料即可，既然這麼單純的話也沒什麼問題了。

問題

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

答案

  1. 方法對，但會TimeOut 
    # Write your MySQL query statement below
SELECT DISTINCT(a.Num) AS ConsecutiveNums
FROM Logs AS a
WHERE ( SELECT z.Num
        FROM Logs AS z
        WHERE z.Id = a.Id + 1) = a.Num
AND (   SELECT z.Num
        FROM Logs AS z
        WHERE z.Id = a.Id + 2) = a.Num
  2. 通過 
    # Write your MySQL query statement below
SELECT DISTINCT(a.Num) AS ConsecutiveNums
FROM Logs AS a
JOIN Logs AS b
ON a.Id = b.Id - 1
JOIN Logs AS c
ON b.Id = c.Id - 1
WHERE a.Num = b.Num
AND b.Num = c.Num

     

       

[MySQL][LeetCode][Hard] 185. Department Top Three Salaries

心得

這題是 184. Department Highest Salary 的衍生題，但如果直接套用其方法的話會TimeOut，不過還是記錄一下方法：

# Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     LIMIT 0, 1) AS TopSalary,
                    (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     AND y.Salary < TopSalary
                     LIMIT 0, 1) AS TwoSalary,
                    (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     AND y.Salary < TwoSalary
                     LIMIT 0, 1) AS ThreeSalary
        FROM Department AS z) AS b
ON a.DepartmentId = b.Id
WHERE ( a.Salary = b.TopSalary
OR      a.Salary = b.TwoSalary
OR      a.Salary = b.ThreeSalary)

問題

The Employee table holds all employees. Every employee has an Id, and there is also a column for the 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            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

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.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

答案

  1. 這個方法是在Top Solutions看到的，在WHERE裡面統計有幾個其他同部門的人大於自己的薪水，把小於三個的都列出來，就是答案。 
    # Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN Department AS b
ON a.DepartmentId = b.Id
WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
        FROM Employee AS z
        WHERE a.DepartmentId = z.DepartmentId
        AND a.Salary < z.Salary) < 3
  2. 同上，只是改成WHERE IN效能較佳 
    # Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN Department AS b
ON a.DepartmentId = b.Id
WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
        FROM Employee AS z
        WHERE a.DepartmentId = z.DepartmentId
        AND a.Salary < z.Salary) IN (0, 1, 2)

     

       

[MySQL][LeetCode][Hard] 262. Trips and Users

心得

這題只是看起來比較多欄位其實沒有多難，題目要求找出除了已經被封鎖的客戶以外的取消率。

問題

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’).

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

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘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 |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

答案

# Write your MySQL query statement below
SELECT  a.Request_at AS Day,
        ROUND(SUM(IF(a.Status = 'completed',0 ,1)) / COUNT(1), 2) AS 'Cancellation Rate'
FROM Trips AS a
JOIN Users AS b
ON a.Client_Id = b.Users_Id
WHERE b.Banned = 'No' 
AND b.Role = 'client'
AND (a.Request_at BETWEEN '2013-10-01' AND '2013-10-03')
GROUP BY a.Request_at

 

       

[MySQL][LeetCode][Medium] 177. Nth Highest Salary

心得

這題非常有趣，題目要求寫一預存程式輸入N並取出排名第N名的薪水為多少，這題與178. Rank Scores差不多，所以可以直接套用。

題目

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

答案

  1. 這題我是拿178. Rank Scores的解法直接套上去取N 
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT a.Salary
      FROM (SELECT (@row_number:=@row_number + 1) AS Rank, z.Salary
            FROM (  SELECT x.* 
                    FROM Employee AS x 
                    GROUP BY x.Salary
                    ORDER BY x.Salary DESC) AS z
            JOIN (SELECT @row_number := 0) AS y) AS a
      WHERE a.Rank = N
  );
END

     

  2. 這解法是由高至低排序了所有的薪水，再用LIMIT來獲取Nth(因為LIMIT是從0開始所以N必須先減一)，非常易懂的解法。 
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    DECLARE M INT;
    SET M = N - 1;
    RETURN (
        # Write your MySQL query statement below.
        SELECT Salary
        FROM Employee
        GROUP BY Salary
        ORDER BY Salary DESC
        LIMIT M, 1
  );
END

     

參考：

  1. [MySQL][LeetCode][Medium] 178. Rank Scores
       

[MySQL][LeetCode][Medium] 184. Department Highest Salary

心得

題目要求找出每個部門薪水最高的員工，若同部門最高薪水有複數則一起顯示，這題我在JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來，這樣在關聯兩張表的時候就可以直接搜尋員工薪水是否等於最高薪資。

問題

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

答案

  1. JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來 
    # Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                     FROM Employee AS y
                     WHERE z.Id = y.DepartmentId
                     LIMIT 0, 1) AS MaxSalary
        FROM Department AS z) AS b
ON a.DepartmentId = b.Id AND a.Salary = b.MaxSalary
  2. (2017/01/16) 寫 185. Department Top Three Salaries 時後發現的方法 
    # Write your MySQL query statement below
SELECT  b.Name AS Department,
        a.Name AS Employee,
        a.Salary AS Salary
FROM Employee AS a
JOIN Department AS b
ON a.DepartmentId = b.Id
WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
        FROM Employee AS z
        WHERE a.DepartmentId = z.DepartmentId
        AND a.Salary < z.Salary) = 0
       

[MySQL][LeetCode][Medium] 178. Rank Scores

心得

題目要求找出成績排名，如果分數相同的話則相同名次，MySQL不像MSSQL有ROW_NUMBER()可以用，只好用個變數來存了。

問題

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.

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

答案

# Write your MySQL query statement below
SELECT a.Score, b.Rank
FROM Scores AS a
JOIN (SELECT (@row_number:=@row_number + 1) AS Rank, z.Score
      FROM (SELECT x.* 
            FROM Scores AS x 
            GROUP BY x.Score
            ORDER BY x.Score DESC) AS z
      JOIN (SELECT @row_number := 0) AS y) AS b
ON a.Score = b.Score  
ORDER BY `b`.`Score` DESC

 

       

[MySQL][LeetCode][Easy] 196. Delete Duplicate Emails

心得

題目要求刪除同樣Email的資料，在DELETE的條件WHERE裡面不能包含異動的表(Person)，所以這裡再包了一層SELECT來排除編譯錯誤的問題，而這層必須給予別名(強制)否則會編譯錯誤。

問題

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
| 3  | [email protected] |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above Person table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
+----+------------------+

答案

DELETE 
FROM    Person
WHERE   Id 
NOT IN (SELECT y.*
        FROM (  SELECT MIN(z.Id)
                FROM Person AS z
                GROUP BY z.Email) AS y)

 

       

[MySQL][LeetCode][Easy] 197. Rising Temperature

心得

題目要求找出溫度比前一天高的資料，這裡用了DATE_SUB與TO_DAYS兩種方式來解答。

題目

Given a Weather table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

答案

  1. Sub Query 
    # Write your MySQL query statement below
SELECT  a.Id
FROM    Weather AS a
WHERE   (   SELECT  z.Temperature
            FROM    Weather AS z
            WHERE   DATE_SUB(a.Date, INTERVAL 1 DAY) = z.Date) < a.Temperature
  2. Join 
    # Write your MySQL query statement below
SELECT  a.Id
FROM    Weather AS a
JOIN    Weather AS b
ON      DATE_SUB(a.Date, INTERVAL 1 DAY) = b.Date
WHERE   a.Temperature > b.Temperature
  3. TO_DAYS 
    # Write your MySQL query statement below
SELECT  a.Id
FROM    Weather AS a
JOIN    Weather AS b
ON      TO_DAYS(a.Date) - TO_DAYS(b.Date) = 1
WHERE   a.Temperature > b.Temperature

     

參考：

  1. DATE_ADD() 與 DATE_SUB() 日期的加法與減法
  2. Mysql日期和時間函數不求人
       

[C#][LeetCode][Easy] 27. Remove Element

心得

跟 [C#][LeetCode][Easy] 283. Move Zeroes 有87% 像。

問題

Given an array and a value, remove all instances of that value in place and return the new length.

Do not allocate extra space for another array, you must do this in place with constant memory.

The order of elements can be changed. It doesn’t matter what you leave beyond the new length.

Example:
Given input array nums = [3,2,2,3], val = 3

Your function should return length = 2, with the first two elements of nums being 2.

答案

public class Solution {
    public int RemoveElement(int[] nums, int val) {
        int j = 0;
        for(int i = 0; i < nums.Length; i++){
            if(nums[i] != val){
                nums[j] = nums[i];
                j++;
            }
        }
        
        return j;
    }
}

 

       

[C#][LeetCode][Easy] 283. Move Zeroes

心得

將數字陣列中為零的數字在不更動其他數字排序的情況下移至陣列最後方，這題要求必須使用原陣列不能new一個新的物件就比較麻煩一點點了。

問題

Given an array nums, write a function to move all 0‘s to the end of it while maintaining the relative order of the non-zero elements.

For example, given nums = [0, 1, 0, 3, 12], after calling your function, nums should be [1, 3, 12, 0, 0].

Note:

  1. You must do this in-place without making a copy of the array.
  2. Minimize the total number of operations.

答案

public class Solution {
    public void MoveZeroes(int[] nums) {
        int j = 0;
        for(int i = 0; i < nums.Length; i++){
            if(nums[i] != 0){
                nums[j] = nums[i];
                j++;
            }
        }
        
        for(int i = j; i < nums.Length; i++){
            nums[i] = 0;
        }
    }
}