瀏覽標籤:

SQL

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

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

答案

 

       

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

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

答案

  1. Sub Query
  2. Join
  3. TO_DAYS

     

參考:

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

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

Table: Orders.

Using the above tables as example, return the following:

答案

  1. LEFT JOIN
  2. Sub Query
       

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

心得:

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

問題:

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

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
  2. DISTINCT > 不重複
  3. GROUP BY > 不重複

     
       

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

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.

答案:

  1. Sub Select
  2. Join
  3. From

     
       

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

心得:

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

問題:

Table: Person

Table: Address

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:

答案:

參考:

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

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

心得:

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

問題:

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

答案:

參考:

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

[MSSQL] 當自動識別欄位需要Insert資料時應該怎麼辦 ?

今天在手動將MySQL資料匯入MSSQL時遇到了這個問題,但又不想將自動識別欄位取消掉,於是乎找了一下資料來解決 …

 

語法:

 

範例:

 

 

參考:

  1. 在程式開發時使用SET IDENTITY_INSERT似乎無效?
  2. SQL錯誤訊息:當IDENTITY_INSERT 設為OFF 時,無法將外顯
       

[MSSQL] 使用 T-SQL 實作分頁

很多表格會遇上資料有上萬筆資料或者上千萬時的大數據,這時如果還是把所有資料撈出來在分頁的話,會造成使用者頁面讀取時間過久,更會有伺服器壓力倍增的問題,這裡提供一個SQL解決方案。

SQL如下:

執行結果:

2016-09-03 01_01_10-SQLQuery9.sql - exfast.me,6000.iPCE_Coding (shuangrain (143))_ - Microsoft SQL S

 

 

參考:

  1. How to return a page of results from SQL?
  2. Select Count(*) / Count(1) / Count(欄位名) 的差異