--1.取出没有写过BookType为science的作者信息。
select a.* from author a
left 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
探讨

SQL code


--1.取出没有写过BookType为science的作者信息。
select a.* from author a
left join 
(select distinct AuthorID from book where BookType='science')
b on a.AuthorID=b.AuthorID where b.ID is null

--……


------解决方案--------------------
--叶子的
SQL code

--1.取出没有写过BookType为science的作者信息。
select a.* from author a
left 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的作者信息。
SQL code
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