编写一条 SQL 查询来找出在同一天阅读至少两篇文章的人。

最新推荐文章于 2023-08-09 22:10:31 发布

a good idea

最新推荐文章于 2023-08-09 22:10:31 发布

阅读量158

点赞数

文章标签：数据库 oracle java

本文链接：https://blog.csdn.net/weixin_40074861/article/details/130650253

版权

-- Create table
create table VIEWTABLE
(
  article_id VARCHAR2(100),
  author_id  VARCHAR2(100),
  viewer_id  VARCHAR2(100),
  view_date  VARCHAR2(100)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column VIEWTABLE.article_id
  is '文章id';
comment on column VIEWTABLE.author_id
  is '作者id';
comment on column VIEWTABLE.viewer_id
  is '读者id';
comment on column VIEWTABLE.view_date
  is '阅读日期';




prompt Importing table VIEWTABLE...
set feedback off
set define off

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values (' 1', '3 ', '5', '2019-08-01');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('3', '4', '5', '2019-08-01');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('1', '3', '6', '2019-08-02');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('2', '7', '7', '2019-08-01');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('2', '7', '6', '2019-08-02');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('4', '7', '1', '2019-07-22');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('3', '4', '4', '2019-07-21');

insert into VIEWTABLE (ARTICLE_ID, AUTHOR_ID, VIEWER_ID, VIEW_DATE)
values ('3', '4', '4', '2019-07-21');

prompt Done.

--编写一条 SQL 查询来找出在同一天阅读至少两篇文章的人。

select temp.viewer_id from (

select vt.view_date, vt.viewer_id , count(vt.article_id) count

from (select distinct * from VIEWTABLE ) vt

group by vt.view_date , vt.viewer_id

) temp where temp.count>=2

select * from VIEWTABLE vt

select distinct view_date,viewer_id as id,count( article_id)
from VIEWTABLE
group by view_date, viewer_id
having count((article_id)) > 1
order by viewer_id;