借书记录

SQL编写问题

借书记录 BORROW(ID,BNUM,BTIME,BFLAG) 借书证号，书号，借阅日期时间，借还标志（‘B' 借书 'R' 还书 ）

求某段时间内（2009-01-01 到 2009-03-01 ） 只借书不还书的 借书证号？[@more@]

一.....

SELECT ID
FROM (SELECT ID
,COUNT(DECODE(BFLAG,'B',ID)) AS B_CNT
,COUNT(DECODE(BFLAG,'R',ID)) AS R_CNT
FROM BORROW
WHERE BTIME BETWEEN DATE '2009-01-01' AND DATE '2009-03-01'
GROUP BY ID
)
WHERE B_CNT>0 AND R_CNT=0;

二...

SELECT *
FROM BORROW b1
WHERE b1.btime BETWEEN to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-03-01','yyyy-mm-dd')
AND b1.BFLAG = 'B'
AND NOT EXISTS(SELECT 1 FROM BORROW b2
WHERE b1.ID=b2.ID
AND b1.BFLAG='R'
AND b1.btime to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-03-01','yyyy-mm-dd')

)
三'''

SELECT ID
FROM BORROW
WHERE BTIME BETWEEN DATE '2009-01-01' AND DATE '2009-03-01'
GROUP BY ID
HAVING COUNT(DECODE(BFLAG,'B',ID))>0 AND COUNT(DECODE(BFLAG,'R',ID))=0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16115580/viewspace-1021628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16115580/viewspace-1021628/