sql - 如何使用sql返回每篇论文的论文标题和作者数量
问题描述
我尝试返回每篇论文的论文标题和作者人数,至少有两篇论文的作者姓名和与陈博士合着的作者姓名。但我找不到任何办法。
作者
CREATE TABLE author(
aid int,
aname VARCHAR(50),
aemail VARCHAR(50) NOT NULL,
affliation VARCHAR(50) NOT NULL,
PRIMARY KEY(aid)
);
编辑
CREATE TABLE Editor (
eid int,
ename VARCHAR(50) NOT NULL,
eemail VARCHAR(50) NOT NULL,
PRIMARY KEY(eid)
);
纸
CREATE TABLE paper (
pid int NOT NULL,
title VARCHAR(50) NOT NULL,
eid int,
submit_date date,
status int NOT NULL,
PRIMARY KEY (pid),
FOREIGN KEY (eid) REFERENCES Editor(eid)
);
论文_作者
create table paper_author (
pid int,
aid int,
primary key(pid,aid),
foreign key(pid) references paper(pid),
foreign key(aid) references author(aid)
)
论文评论
create table paper_review (
prid int,
pid int,
rid int,
due_date date,
receive_date date,
round int,
decision int,
rcomment varchar(200),
primary key(prid),
foreign key(pid) references paper(pid),
foreign key(rid) references reviewer(rid)
)
审稿人
create table Reviewer (
rid int,
rname varchar(50),
remail varchar(50),
raffiliation varchar(50),
primary key(rid)
)
样本输入
作者
INSERT INTO dbo.author (aid,aname,aemail,affliation) VALUES(1,'Dr. Chen','chen@umbc.edu','UMBC');
INSERT INTO dbo.author (aid,aname,aemail,affliation)values(2,'Susan','susan@umbc.edu','UMBC');
INSERT INTO dbo.author (aid,aname,aemail,affliation)values(3,'Steve','steve@umb.edu','UMB');
INSERT INTO dbo.author (aid,aname,aemail,affliation) values(4,'Carole','carole@umb.edu','UMB');
编辑
insert into editor (eid,ename,eemail) values(1,'Claire','claire@gmail.com');
insert into editor (eid,ename,eemail) values(2,'David','david@gmail.com');
纸
INSERT INTO paper (pid,title,eid,submit_date,status) values(1,'Comparing big data systems',2, '2020-1-10',3);
INSERT INTO paper (pid,title,eid,submit_date,status) values(2,'A novel approach of mining EHR data',2, '2020-2-10',2);
INSERT INTO paper (pid,title,eid,submit_date,status) values(3, 'A new SQL Benchmark', 1, '2020-1-1',1);
论文作者
INSERT INTO paper_author(pid,aid) values(1,1);
INSERT INTO paper_author(pid,aid) values(1,2);
INSERT INTO paper_author(pid,aid) values(2,3);
INSERT INTO paper_author(pid,aid) values(2,4);
INSERT INTO paper_author(pid,aid) values(3,1);
INSERT INTO paper_author(pid,aid) values(3,2);
论文评论
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(2, 1, 3, '2020-2-10', '2020-2-10',1,2,'Good paper fix a few typo');
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(1, 1, 1, '2020-2-10', '2020-2-9',1,3,'Good paper but needs to improve writing');
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(3, 1, 4, '2020-2-10', '2020-2-12',1,3,'Please add more experiments');
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(4, 2, 2, '2020-3-10',null,1,null,null);
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(5, 2, 1, '2020-3-10',null,1,null,null);
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(6, 2, 3, '2020-3-10',null,1,null,null);
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(7, 3, 4, '2020-2-1', '2020-1-15',1,2,'good paper, fix a few typo');
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(8, 3, 2, '2020-2-1', '2020-1-11',1,3,'good paper, but please add more related work');
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(9, 3, 4, '2020-3-1', '2020-2-15',2,1,'all comments addressed');
INSERT INTO paper_review (prid,pid,rid,due_date,receive_date,round,decision,rcomment) values(10, 3, 2,'2020-3-1', '2020-2-18',2,1,'all comments addressed');
审稿人
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(1,'Ellen','ellen@gmail.com','Johns Hopkins');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(2,'Cathy','cathy@gmail.com','Johns Hopkins');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(3,'Grace','grace@gmail.com','Stanford');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(4,'Eric','eric@gmail.com','Stanford');
INSERT INTO reviewer(rid,rname,remail,raffiliation) values(5,'Ethan','ethan@umbc.edu','UMBC');
解决方案
我的理解是您正在寻找或三个不同的答案。
每篇论文的论文标题和作者数量:
select p.Title as Paper_Title, count(pa.aid) No_Of_Authors from paper p inner join paper_author pa on p.pid=pa.pid
group by p.title;
输出:
至少有两篇论文的作者姓名:
select AName from author a inner join paper_author pa on a.aid=pa.aid
group by AName
having count(*)>=2;
输出:
列出与陈博士合着的作者姓名:
select Distinct a.* from author a inner join paper_author pa on a.aid=pa.aid
where exists (select 1 from Author aa inner join paper_author paa on aa.aid=paa.aid
where aname='Dr. Chen' and paa.pid=pa.pid and aa.aid<>a.aid);
输出:
推荐阅读
- c - typedef const 结构中的语法错误
- azure - Azure 流分析:连接行
- javascript - Django 模板和 jquery
- android - Android Studio:Textview 未显示在某些主题中,如(AppTheme,AppCompat),但在其他主题中可见/显示
- javascript - 如何从 JavaScript 中的对象数组中获取重复值?
- java - SpringBootTest 排除包被扫描
- amazon-web-services - 从另一个 AWS 账户的 S3 存储桶调用 lambda
- android-emulator - 使用按钮添加选项卡的错误在哪里?
- r - 如何将(gam)图中的y轴值从平滑值更改为实际值?
- python - 将 KERAS 张量转换为 K.tf.int32