SELECT 之後的查詢

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

SELECT 之後的處理順序

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

不見得全選最好

SELECT *
FROM PlayerStats;

SELECT * 有利於資料探索，但可能影響效能

PlayerName	Team	Position	...	...
Alex Abrines	OKC	SG	...	...
Quincy Acy	BRK	PF	...	...
Steven Adams	OKC	C	...	...
Bam Adebayo	MIA	C	...	...
...	...	...	...	...

只取所需更好

SELECT PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats;

只選需要的欄位

PlayerName	Team	TotalPoints
Alex Abrines	OKC	353
Quincy Acy	BRK	411
Steven Adams	OKC	1056
Bam Adebayo	MIA	477
...	...	...

JOIN 全選的問題

SELECT * 
FROM Players p
  INNER JOIN PlayerStats ps
  ON ps.PlayerName = p.PlayerName;

在 JOIN 中使用 SELECT * 會重複輸出連結欄位

PlayerName	Age	PlayerName	Team	...
Alex Abrines	24	Alex Abrines	OKC	...
Quincy Acy	27	Quincy Acy	BRK	...
Steven Adams	24	Steven Adams	OKC	...
Bam Adebayo	20	Bam Adebayo	MIA	...
...	...	...	...	...

JOIN 中的必要欄位

SELECT p.PlayerName, 
       ps.Team, 
       p.Country, 
       ps.Position
FROM Players p
  INNER JOIN PlayerStats ps
  ON ps.PlayerName = p.PlayerName;

明確指定要回傳的欄位與所屬資料表

PlayerName	Country	Team	Position
Alex Abrines	Spain	OKC	SG
Quincy Acy	USA	BRK	PF
Steven Adams	New Zealand	OKC	C
Bam Adebayo	USA	MIA	C
...	...	...	...

前幾列（TOP）

SELECT TOP 5 PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats;

PlayerName	Team	TotalPoints
Alex Abrines	OKC	353
Quincy Acy	BRK	411
Steven Adams	OKC	1056
Bam Adebayo	MIA	477
Arron Afflalo	ORL	179

依百分比取 TOP

SELECT TOP 1 PERCENT PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats;

PlayerName	Team	TotalPoints
Alex Abrines	OKC	353
Quincy Acy	BRK	411
Steven Adams	OKC	1056
Bam Adebayo	MIA	477
Arron Afflalo	ORL	179
Cole Aldrich	MIN	12
LaMarcus Aldridge	SAS	1735

沒有排序就沒有「前幾名」

SELECT TOP 5 PlayerName, 
        Team, 
        TotalPoints
FROM PlayerStats
ORDER BY TotalPoints DESC

PlayerName	Team	TotalPoints
LeBron James	CLE	2251
James Harden	HOU	2191
Anthony Davis	NOP	2110
Russell Westbrook	OKC	2028
Giannis Antetokounmpo	MIL	2014

其他列數限制語法

列數限制	排序子句	資料庫
TOP	ORDER BY	Microsoft SQL Server
ROWNUM	ORDER BY	Oracle
LIMIT	ORDER BY	PostgreSQL

何時使用 ORDER BY

在查詢中使用 ORDER BY：

用於資料探索
當最終結果需要排序時

一起來練習吧！

改進 SQL Server 中的查詢效能