2021-05-18

文章浏览

需求一：找出所有浏览过自己文章的作者，结果按照 id 升序排列。

展示效果：

id
4
7

Create table If Not Exists 53_Views (article_id int, author_id int, viewer_id int, view_date date);
Truncate table 53_Views;
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (1, 3, 5, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 5, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (1, 3, 6, '2019-08-02');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (2, 7, 7, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (2, 7, 6, '2019-08-02');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (4, 7, 1, '2019-07-22');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 4, '2019-07-21');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 4, '2019-07-21');

最终SQL:

select
	distinct viewer_id id
from
	53_Views
where
	author_id = view_date
order by
	viewer_id desc

需求二：找出在同一天阅读至少两篇文章的人，结果按照 id 升序排序。

展示效果：

project_id
5
6

最终SQL:

select 
	distinct viewer_id id
from
	53_Views
group by
	viewer_id, view_date
having 
	count(distinct author_id ) >= 2
order by
	viewer_id desc