第1个回答 2009-10-13
1. 借书学生的学生编号会出现在borrow表里面。
select stuID,stuName,major
from student
where stuID in
(
select stuID from borrow
)
2. 未规还的记录B_time字段应该是NULL值。
select stuname,count(*) as 借书数量
from student as a join borrow as b
on a.stuid=b.stuid
where b.B_time is NULL
group by b.stuid,a.stuname
3. select stuname,T_time,
(case when B_time is null then '尚未归还' else cast(datediff(day,T_time,B_time) as varchar(5)) end) as 借阅天数
from student a join borrow b
on a.stuid=b.stuid
join book c
on b.bookid=c.bookid
where bookname='天龙八部'
我自己测试过,符合要求,你再看看,希望对你有帮助,互相学习~本回答被提问者采纳
第2个回答 2009-10-13
--1
SELECT stuID,stuName, major FROM STUENT
WHERE STUID IN (SELECT STUID FROM borrow)
--1_1
SELECT A.stuID,A.stuName, A.major FROM STUENT A,borrow B
WHERE A.STUID=B.STUID
--2
borrow:字段borrowID(结束编号),stuID(学生编号),BID(图书编号), T_time (结束日期),B_time(还书日期)
SELECT A.stuName, COUNT(BID) AS 图书数量 FROM STUENT A,borrow B
WHERE A.STUID=B.STUID AND GETDATE()>=B.B_TIME)
GROUP BY A.stuName
--3
SELECT A.stuName, B.T_TIME,(case b_time is null when then
DAY(B.B_TIME-T_TIME)
else '尚未归还'
end) AS 看书天数 FROM STUENT A,borrow B,BOOK C
WHERE A.STUID=B.STUID AND B.BID=C.BID
AND C.TITLE='《天龙八部》'