SQLBolt：https://sqlbolt.com/lesson/introduction

• SQL Lesson 4: Filtering and sorting Query results

使用語法

• DISTINCT
• ORDER BY
• LIMIT

SQL Lesson 4: Filtering and sorting Query results

今天的題目，有提到我們在 Day 14 所使用的 ORDER BY & LIMIT。

也有我們沒有用過的 DISTINCT。

我們先直接來解題：

題目 1

-- 1. List all directors of Pixar movies (alphabetically), without duplicates 
SELECT DISTINCT director FROM movies
ORDER BY director;

-- 筆者解法（搭配 GROUP BY ）：
SELECT director FROM movies
GROUP BY director;

題目 2

-- 2. List the last four Pixar movies released (ordered from most recent to least)
SELECT * FROM movies
ORDER BY year DESC
LIMIT 4;

題目 3

-- 3. List the first five Pixar movies sorted alphabetically
SELECT * FROM movies
ORDER BY title 
LIMIT 5;

題目 4

-- 4. List the next five Pixar movies sorted alphabetically
SELECT * FROM movies
ORDER BY title 
LIMIT 5 OFFSET 5;

-- 筆者解法
SELECT * FROM movies
ORDER BY title 
LIMIT 5, 5;

題解

DISTINCT

來稍微說明一下，DISTINCT。

SQLBolt 的說明如下：

Since the DISTINCT keyword will blindly remove duplicate rows, we will learn in a future lesson how to discard duplicates based on specific columns using grouping and the GROUP BY clause.

• DISTINCT 的功能是「去除重複值」，也就是在返回查詢結果前，去除重複的紀錄。
• DISTINCT 是去檢視所有的欄位，僅保留唯一值。
• DISTINCT 是無條件去重，

LIMIT

來解釋一下 LIMIT 語句的用法：

SELECT * FROM movies
ORDER BY title 
LIMIT M, N;

• 從第 M 筆開始，要撈 N 筆資料

SELECT * FROM movies
ORDER BY title 
LIMIT M OFFSET N;

• 撈 M 筆資料，從第 N 筆開始

參考資料：

• SQLBlot - Lesson 4
• Day 14 選取資料 - ORDER BY、LIMIT
• SQL Distinct（SQL語法教學）
• MySQL 學習筆記