題目敘述
題目會給我們一張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
