[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_SUBTO_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;
        }
    }
}
       

[MySQL][LeetCode][Easy] 183. Customers Who Never Order

心得

這題要show出沒有訂過東西的客戶,所以可以使用LEFT JOIN來關聯兩張表。

問題

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

答案

  1. LEFT JOIN
    # Write your MySQL query statement below
    SELECT      a.Name AS Customers
    FROM        Customers AS a
    LEFT JOIN   Orders AS b
    ON          a.Id = b.CustomerId
    WHERE       b.CustomerId IS NULL
  2. Sub Query
    # Write your MySQL query statement below
    SELECT  a.Name AS Customers
    FROM    Customers AS a
    WHERE   (   SELECT  COUNT(1)
                FROM    Orders AS z
                WHERE   z.CustomerId = a.Id) = 0
       

[MySQL][LeetCode][Easy] 176. Second Highest Salary

心得:

找出該資料表內第二高薪水的筆數,若無則回傳null。

問題:

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

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

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

答案:

  1. Sub Query
    # Write your MySQL query statement below
    SELECT  MAX(a.Salary) AS SecondHighestSalary
    FROM    Employee AS a
    WHERE   a.Salary < (SELECT  MAX(z.Salary)
                        FROM    Employee AS z)
  2. DISTINCT > 不重複
    # Write your MySQL query statement below
    SELECT
    (
        SELECT DISTINCT Salary
        FROM            Employee
        ORDER BY        Salary DESC
        LIMIT           1, 1
    ) AS SecondHighestSalary
  3. GROUP BY > 不重複
    # Write your MySQL query statement below
    SELECT
    (
        SELECT          Salary
        FROM            Employee
        GROUP BY        Salary
        ORDER BY        Salary DESC
        LIMIT           1, 1
    ) AS SecondHighestSalary

     

       

[MySQL][LeetCode][Easy] 181. Employees Earning More Than Their Managers

心得:

題目要求找出有哪些人的薪水比主管高,找出名字並修改欄位名稱為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.

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

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.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

答案:

  1. Sub Select
    # Write your MySQL query statement below
    SELECT  a.Name AS Employee
    FROM    Employee AS a
    WHERE   a.Salary > (SELECT  z.Salary 
                        FROM    Employee AS z
                        WHERE   z.Id = a.ManagerId);
  2. Join
    # Write your MySQL query statement below
    SELECT  a.Name AS Employee
    FROM    Employee AS a
    JOIN    Employee AS b
    ON      a.ManagerId = b.Id
    WHERE   a.Salary > b.Salary
  3. From
    # Write your MySQL query statement below
    SELECT  a.Name AS Employee
    FROM    Employee AS a, Employee AS b
    WHERE   a.ManagerId = b.Id
    AND     a.Salary > b.Salary

     

       

[MySQL][LeetCode][Easy] 175. Combine Two Tables

心得:

題目要求找出Person表內所有人員的地址,就算是空的也無所謂,所以不用理會Address表內是否有Person表的資料,這題必須使用LEFT JOIN來關聯。

問題:

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for
each person in the Person table, regardless if there is an address for each
of those people:

FirstName, LastName, City, State

答案:

# Write your MySQL query statement below
SELECT      a.FirstName, a.LastName, b.City, b.State
FROM        Person AS a
LEFT JOIN   Address AS b
ON          a.PersonId = b.PersonId

參考:

  1. [SQL] 一張圖片解釋 JOIN