SQL 條件語法 文章的讀者 I Leetcode_#1148

Nick Morrison on Unsplash

題目敘述

題目會給我們一張Views資料表。裡面分別有article_id、author_id、viewer_id、view_date等欄位。題目說這張資料表沒有主鍵Ｐrimary key，而且可能有重複欄位。

題目要求我們列出所有讀過自己寫的文章的作者ID

輸出答案時，請以作者ID做升序排列。

Table: Views

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
There is no primary key (column with unique values) for this table, the table may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date. 
Note that equal author_id and viewer_id indicate the same person.

詳細的題目可在這裡看到

約束條件

列出所有讀過自己寫的文章的作者ID

輸出答案時，請以作者ID做升序排列。

演算法

SELECT ...欄位

FROM ...資料表

WHERE ...篩選條件

依照題意帶入即可，最後記得加上

ORDER BY id ASC;

以 作者ID 做升序排列。

程式碼

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id ASC;

關鍵知識點

聯想到SQL查詢的標準模板，依題意帶入即可。

​SELECT ...欄位
FROM ...資料表
WHERE ...篩選條件

Reference:

[1] MySQL by SELECT DISTINCT ..., and ORDER BY - Article Views I - LeetCode