SQL排序語法 列出不無聊的電影 Leetcode #620

charlesdeluvio null on Unsplash

題目敘述

題目會給我們一張Cinema資料表，裡面分別有id、movie、description, rating 等欄位，其中id 是主鍵Primary Key。

要求我們列出所有推薦人ID為奇數，而且不無聊的電影，印出時依照電影rating評分從高到低降序排列。

Table: Cinema

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| id             | int      |
| movie          | varchar  |
| description    | varchar  |
| rating         | float    |
+----------------+----------+
id is the primary key (column with unique values) for this table.
Each row contains information about the name of a movie, its genre, and its rating.
rating is a 2 decimal places float in the range [0, 10]

詳細的題目可在這裡看到

測試範例

Example 1:

Input: 
Cinema table:
+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 1  | War        | great 3D    | 8.9    |
| 2  | Science    | fiction     | 8.5    |
| 3  | irish      | boring      | 6.2    |
| 4  | Ice song   | Fantacy     | 8.6    |
| 5  | House card | Interesting | 9.1    |
+----+------------+-------------+--------+
Output: 
+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 5  | House card | Interesting | 9.1    |
| 1  | War        | great 3D    | 8.9    |
+----+------------+-------------+--------+
Explanation: 
We have three movies with odd-numbered IDs: 1, 3, and 5. The movie with ID = 3 is boring so we do not include it in the answer.

約束條件

列出所有推薦人ID為奇數，而且不無聊的電影。

印出時依照電影rating評分從高到低降序排列。

演算法

入門題，使用基礎的SELECT ...欄位 FROM ...表格 WHERE ...條件 SQL查詢語法。

​排序時，使用 ORDER BY 排序依據的欄位 依著題意 放入 ASC升序 / DESC 降序 關鍵字

如果是第一次接觸SQL的同學，請到這邊學習基本的SQL 語法。

程式碼

SELECT id, movie, description, rating
FROM Cinema
WHERE id%2 = 1 AND description <> "boring"
ORDER BY rating DESC;

關鍵知識點

掌握基本的SELECT ...欄位 FROM ...表格 WHERE ...條件 ，和

排序語法 ORDER BY 欄位 ASC 升序 / DESC 降序 即可。

Reference:

[1] MySQL solution by SELECT ... WHERE ... ORDER BY - Not Boring Movies - LeetCode