首页 新闻 会员 周边 捐助

查看在7月份作者“谭浩强“没被人借过的书有哪些?

0
悬赏园豆:20 [已解决问题] 解决于 2011-02-21 17:54

select b.*
from Books b,Record r
where b.bookid=r.bookid and datepart(mm,borrowbook)=7 and bookwriter='谭浩强'

我把7月被人借过的书查出来了..

但没有被借过的书应该怎么写去了,高手指点一下...

蝸牛漫步的主页 蝸牛漫步 | 初学一级 | 园豆:0
提问于:2011-02-19 18:21
< >
分享
最佳答案
0

这样试试.

select *
from Books
where bookwriter='谭浩强' and
bookid not
in (
select bookid
from Record inner join Books
on Record.bookid
=Books.bookid
where datepart(mm,borrowbook)=7 and Books.bookwriter='谭浩强'
)
收获园豆:20
Localhost | 菜鸟二级 |园豆:443 | 2011-02-21 08:34
select * from Books where bookwriter='谭浩强' and Books.bookid not in(select Record.bookid from Record inner join Books on Record.bookid=Books.bookid where datepart(mm,borrowbook)=7 and Books.bookwriter='谭浩强')
蝸牛漫步 | 园豆:0 (初学一级) | 2011-02-21 17:52
谢谢了!不过我用另外的一种方法也做出来了.. select bookid,bookname,bookwriter from ( select b.*, case when month(r.borrowbook)=7 then 1 else 0 end as borrowFalg, case when b.bookwriter ='谭浩强' then 1 else 0 end as writerFalog from books b left join record r on b.bookid=r.bookid ) as queryA group by bookid,bookname,bookwriter,writerFalog having max(borrowFalg)<>1 and writerFalog=1
蝸牛漫步 | 园豆:0 (初学一级) | 2011-02-21 17:54
其他回答(1)
0

--使用NOT EXISTS,查询出在借阅记录Record中没有记录的就行了。select b.* from Books bwhere not exists(select 1 from Record rwhere r.bookid=b.bookid)

James.H.Fu | 园豆:90 (初学一级) | 2011-02-20 12:41
题目这个--3、查看在7月份作者“谭浩强“没被人借过的书有哪些?
支持(0) 反对(0) 蝸牛漫步 | 园豆:0 (初学一级) | 2011-02-20 20:36
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册