瀏覽標籤:

SQL

[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(欄位名) 的差異
       

[MSSQL] SQL Server Management Studio 連線非預設 1433 port 埠號

參考:SQL Server 透過 TCP/IP 遠端連線時如何使用非 1433 埠號

 

未命名

自從把SQL Server放到公用網路上的時候,就常常被人暴力破解Try我的密碼,雖然不至於被猜到,但是看了總是不太開心,於是乎就把預設的Port修改掉了,卻發生使用SQL Server Management Studio無法連線的問題,之前用MySQL時格式不外乎就是在後面加上:port但轉來MSSQL卻發生無法連線的問題…

 

爬了一下文才發現,使用ip,port(127.0.0.1,4321)這種格式就可以正常連線囉!
2016-08-20 18_18_00-連接到伺服器