瀏覽標籤：

MySQL

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

作者lex.xu
/
1 月 13, 2017
/
心得筆記

心得

題目要求找出成績排名，如果分數相同的話則相同名次，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 | +----+-------+

1
2
3
4
5
6
7
8
9
10

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

1
2
3
4
5
6
7
8
9
10

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

# 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

作者lex.xu
/
1 月 13, 2017
/
心得筆記

心得

題目要求刪除同樣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 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id is the primary key column for this table.

1
2
3
4
5
6
7
8

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
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 | john@example.com | | 2 | bob@example.com | +----+------------------+

1
2
3
4
5
6

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

答案

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

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

作者lex.xu
/
1 月 13, 2017
/
心得筆記

心得

題目要求找出溫度比前一天高的資料，這裡用了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 | +---------+------------+------------------+

1
2
3
4
5
6
7
8

+---------+------------+------------------+
| 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
2
3
4
5
6

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

答案

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

# 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

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

# 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

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

# 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

參考：

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

作者lex.xu
/
1 月 13, 2017
/
心得筆記

心得

這題要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 | +----+-------+

1
2
3
4
5
6
7
8

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

Table: Orders.

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

1
2
3
4
5
6

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

Using the above tables as example, return the following:

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

1
2
3
4
5
6

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

答案

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

# 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

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

# 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

作者lex.xu
/
1 月 12, 2017
/
心得筆記

心得：

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

問題：

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

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

1
2
3
4
5
6
7

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

答案：

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)

# 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)

DISTINCT > 不重複

# Write your MySQL query statement below
SELECT
(
    SELECT DISTINCT Salary
    FROM            Employee
    ORDER BY        Salary DESC
    LIMIT           1, 1
) AS SecondHighestSalary

# Write your MySQL query statement below

SELECT

(

SELECT DISTINCT Salary

FROM Employee

ORDER BY Salary DESC

LIMIT 1, 1

) AS SecondHighestSalary

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

# 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

作者lex.xu
/
1 月 11, 2017
/
心得筆記

心得：

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

1
2
3
4
5
6
7
8

+----+-------+--------+-----------+
| 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
2
3
4
5

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

答案：

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);

# 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);

Join

C#

# 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

1
2
3
4
5
6

# 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
From

C#

# 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

1
2
3
4
5

# 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

作者lex.xu
/
1 月 10, 2017
/
心得筆記

心得：

題目要求找出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.

1
2
3
4
5
6
7
8

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

1
2
3
4
5
6
7
8
9

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

1

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

# 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

參考：

[SQL] 一張圖片解釋 JOIN

[MySQL][LeetCode][Easy] 182. Duplicate Emails

作者lex.xu
/
12 月 30, 2016
/
心得筆記

心得：

題目要求找出Email重複的資料並Show出來，這裡要注意的是沒辦法使用WHERE而必須使用HAVING，因為WHERE在GROUP BY前面，HAVING在GROUP BY後面。

問題：

Write a SQL query to find all duplicate emails in a table named Person.

答案：

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1

SELECT Email

FROM Person

GROUP BY Email

HAVING COUNT(Email) > 1

參考：

SQL語法中WHERE與HAVING有何差異?

[MySQL] INSERT資料前檢查資料是否重複

作者lex.xu
/
7 月 08, 2016
/
心得筆記

有時候需要檢查資料是否重複再填入，這裡簡單做個筆記。

程式碼

INSERT INTO `table` (`title`, `url`, `photocount`, `uploadtime`, `findtime`)
SELECT @title, @url, @photocount, @uploadtime, @findtime
FROM DUAL
WHERE NOT EXISTS (	SELECT 1
			FROM `table`
			WHERE `title` = @title
                    	AND `url` = @url);

INSERT INTO `table` (`title`, `url`, `photocount`, `uploadtime`, `findtime`)

SELECT @title, @url, @photocount, @uploadtime, @findtime

FROM DUAL

WHERE NOT EXISTS ( SELECT 1

FROM `table`

WHERE `title` = @title

AND `url` = @url);

SELECT @title, @url, @photocount, @uploadtime, @findtime

1	SELECT @title, @url, @photocount, @uploadtime, @findtime

SELECT後面是準備填入的參數

WHERE NOT EXISTS (	SELECT 1
			FROM `table`
			WHERE `title` = @title
                    	AND `url` = @url);

WHERE NOT EXISTS ( SELECT 1

FROM `table`

WHERE `title` = @title

AND `url` = @url);

WHERE這邊驗證資料是否重複

來源：MySQL INSERT 與 UPDATE 的一些特殊情況用法

[C#][ASP.NET MVC5] MySQL Entity Framework 學習筆記(一) – 環境設定

作者lex.xu
/
6 月 08, 2016
/
心得筆記

剛開始在碰ASP.NET MVC的時候，查詢該如何連結資料庫時常常會聽到Entity Framework這個方法，但由於那時沒有太多時間，沒去查太多資料都用SQL自幹，現在閒下來了，就來玩玩看這個東西，首先是環境設定：

從NuGet安裝MySQL使用Entity Framework時所需要的套件
總共有三個東西要安裝，分別是
- MySql.Data
- MySql.Data.Entity
- MySql.Web

安裝完畢後就要開始設定連結字串，打開Web.config新增連結字串

<connectionStrings>
	<add name="MySqlConnectionString" providerName="MySql.Data.MySqlClient" connectionString="Server=server;Database=dbname;UID=username;Password=password" />
</connectionStrings>

</connectionStrings>

建立MySqlContext.cs並繼承DbContext供後續使用

C#

public class MySqlContext : DbContext { public MySqlContext() : base("MySqlConnectionString") { } }

1
2
3
4

public class MySqlContext : DbContext
{
public MySqlContext() : base("MySqlConnectionString") { }
}

結論：

這樣環境就建置完成囉，後續就可以開始使用Entity Framework了！

參考：