查看在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
这样试试.
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
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='谭浩强')
谢谢了！不过我用另外的一种方法也做出来了..
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
其他回答(1)
0
--使用NOT EXISTS,查询出在借阅记录Record中没有记录的就行了。select b.* from Books bwhere not exists(select 1 from Record rwhere r.bookid=b.bookid)
题目这个--3、查看在7月份作者“谭浩强“没被人借过的书有哪些？
