分享一道易贸集团的SQL笔试题
请观察两张表的结构，并回答问题。
表book结构如下：
ID（int），BookName（varchar（50）），AuthorID（int），BookType（varchar(20)），PublishTime（Datetime）
表author结构如下：
AuthorID(int)，AuthorName（varchar(50)）,Address(varchar(100)),Tele(varchar(50))
问题：
1.取出没有写过BookType为science的作者信息。
2.取出每个作者按照PublishTime倒排序前5名的书名。
[解决办法]
1、select AuthorID as 作者ID，AuthorName as 作者姓名,Address as 地址,Tele as 联系电话
from author as a
inner join
book as b
on a.AuthorID=b.AuthorID
where
b.BookType='science'
2、select top(BookName) as 书名 from book order by desc
[解决办法]
--1.取出没有写过BookType为science的作者信息。select a.* from author aleft join (select distinct AuthorID from book where BookType='science')b on a.AuthorID=b.AuthorID where b.ID is null--2.取出每个作者按照PublishTime倒排序前5名的书名。select * from book a where ID in (select top 5 ID from book where AuthorID =a.AuthorID order by PublishTime desc)
[解决办法]
+1
--1.取出没有写过BookType为science的作者信息。select a.* from author aleft join (select distinct AuthorID from book where BookType='science')b on a.AuthorID=b.AuthorID where b.ID is null--2.取出每个作者按照PublishTime倒排序前5名的书名。select * from book a where ID in (select top 5 ID from book where AuthorID =a.AuthorID order by PublishTime desc)
[解决办法]
1.取出没有写过BookType为science的作者信息。
select a.* from author a where not exists(select 1 from book b where b.BookType = 'science' and b.AuthorID = a.AuthorID)
[解决办法]
取出每个作者按照PublishTime倒排序前5名的书名。
select top 5 b.BookName from book b left join author a where a.AuthorID = b.AuthorID order by PublishTime desc
[解决办法]
/*
表book结构如下：
ID（int），BookName（varchar（50）），
AuthorID（int），BookType（varchar(20)），
PublishTime（Datetime）
表author结构如下：
AuthorID(int)，
AuthorName（varchar(50)）,
Address(varchar(100)),
Tele(varchar(50))
问题：
1.取出没有写过BookType为science的作者信息。
2.取出每个作者按照PublishTime倒排序前5名的书名。
*/
go
if OBJECT_ID('book')is not null
drop table book
go
create table book(
ID int,
BookName varchar(50),
AuthorID int,
BookType varchar(20),
PublishTime datetime
)
go
insert book
select 1,'A',1,'science','2009-05-14' union all
select 2,'B',2,'prose','2012-02-23' union all
select 3,'C',3,'essay','2011-12-31' union all
select 4,'D',1,'science','2010-06-25' union all
select 5,'E',1,'essay','2008-12-31' union all
select 6,'F',2,'essay','2006-06-06' union all
select 7,'G',3,'essay','2010-12-12'
go
if OBJECT_ID('author')is not null
drop table author
go
create table author(
AuthorID int,
AuthorName varchar(50),
[Address] varchar(100),
Tele varchar(50)
)
go
insert author
select 1,'tracy','辽宁大连','13624098060' union all
select 2,'lucy','辽宁沈阳','13521458974' union all
select 3,'tom','四川成都','15091522320'
--1.取出没有写过BookType为science的作者信息。
select *from author where AuthorID not in
(select distinct AuthorID from book where BookType='science')
/*
AuthorIDAuthorNameAddressTele
2lucy辽宁沈阳13521458974
3tom四川成都15091522320
*/
--2.取出每个作者按照PublishTime倒排序前5名的书名。
select b.ID,b.BookName,b.AuthorID,b.BookType,b.PublishTime from
(select *,ROW_NUMBER()OVER(partition by AuthorID order by PublishTime desc)
as num from book)b
where num<=5
/*
IDBookNameAuthorIDBookTypePublishTime
4D1science2010-06-25 00:00:00.000
1A1science2009-05-14 00:00:00.000
5E1essay2008-12-31 00:00:00.000
2B2prose2012-02-23 00:00:00.000
6F2essay2006-06-06 00:00:00.000
3C3essay2011-12-31 00:00:00.000
7G3essay2010-12-12 00:00:00.000
*/
