LeetCode MySQL-1148. Article Views I

    AllenLi
    LeetCode MySQL

    Write an SQL query to find all the authors that viewed at least one of their own articles.
    Return the result table sorted by id in ascending order.

    寫一個 SQL Query來尋找至少看過一個自己文章的所有作家。
依據 id 來排序結果。

    Table: Views

    +---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
There is no primary key for this table, it 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.

    Example 1:

    Input: 
Views table:
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+
Output: 
+------+
| id   |
+------+
| 4    |
| 7    |
+------+

    Solution:
    1. 選擇標題 不重複的 author_id
    2. 來自 Views 的 table
    3. 且符合參訪人的紀錄

    Code1:

    # DISTINCT

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

    Code2:

    # GROUP BY

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

    newViews

    Related Posts