请教：视图或sp

[takecare]

我现在有一个表T记录学生借书/还书记录，表结构描述如下：

自动编号(sid)，学生编号(uid)，书籍编号(bid)，时间戳(tm)，时间标记（借/还）(flag)

现在我想要查询学生借用某本书多长时间，我如何做呢？目前表结构已经定下来了，不能修改了。

[lyyrw]

select a.sid,a.uid,a.bid,datediff(借书天数,a.tm,isnull(b.tm,getdate()))
from (select uid, bid, min(tm) from t where flag='借' group by uid,bid) a
left jion
(select uid, bid, min(tm) from t where flag='还' group by uid,bid) b
on a.uid=b.uid and a.bid=b.bid

[takecare]

谢谢各位的热心回答！
我从上面各位给出的结果来看，确实能将相应的借/还匹配起来，但是如果我重复借/还某一本书的话，好像只能看到最早的那条记录。而不能将所有借/还信息全部检索来。

[zjcxc]

可以的.
select a.uid,a.bid,
借书天数=datediff(day,a.tm,isnull(b.tm,getdate())),
状态=case when b.tm is null then '未还' else '已还' end
from
(
select sid=(select sum(1) from t where uid=aa.uid and bid=aa.bid and sid<=aa.sid)
,uid,bid,tm from t aa where flag='借'
) a inner join (
select sid=(select sum(1) from t where uid=aa.uid and bid=aa.bid and sid<=aa.sid)
,uid,bid,tm from t aa where flag='还'
) b on a.uid=b.uid and a.bid=b.bid and a.sid=b.sid

[hjb111]

请问时间戳(tm)里的内容？

[hjb111]

请问时间戳(tm)里的内容，可能用datediff函数求时间差异！

[愉快的登山者]

select A.uid, A.bid, A.tm, B.tm, datediff(day, A.tm, B.tm) 借用多长时间
from 表T A left join 表T B
on A.uid = B.uid
and A.bid = B.bid
where A.flag = '借'
and B.flag = '还'
and B.tm = (select min(tm) from 表T where uid = A.uid and bid = A.bid and flag = '还')

[dafu71]

修改一下
select A.uid, A.bid, A.tm, B.tm, datediff(day, A.tm, B.tm) 借用多长时间
from 表T A left join 表T B
on A.uid = B.uid
and A.bid = B.bid
where A.flag = '借'
and B.tm = (select isnull(min(tm),getdate()) from 表T where uid = A.uid and bid = A.bid and flag = '还')

[sdhdy]

--一种是借了，还了

--一种是借了,还没还

select a.sid,a.uid,a.bid,datediff(d,a.tm,isnull(b.tm,getdate())),b.flag
from t a
left join t b on a.uid=b.uid and a.bid=b.bid and b.flag='还'
where a.flag='借'

[chao778899]

select A.uid, A.bid, A.tm, B.tm, datediff(day, A.tm, B.tm) 借用多长时间
from 表T A inner join 表T B
on A.uid = B.uid
and A.bid = B.bid
where A.flag = '借'
and B.flag = '还'
and A.sid in (select top 2 sid from 表T where where uid = A.uid and bid = A.bid order by sid)

[chao778899]

select a.sid,a.uid,a.bid,datediff(d,a.tm,isnull(b.tm,getdate())),b.flag
from t a
left join t b on a.uid=b.uid and a.bid=b.bid and b.flag='还'
where a.flag='借'
and A.sid in (select top 2 sid from 表T where where uid = A.uid and bid = A.bid order by sid)
借了还－－借了没还－－－借了还，还了借，借了还

[zjcxc]

select uid,bid,
借书天数=datediff(day,a.tm,isnull(b.tm,getdate()),
状态=case when b.tm is null then '未还' else '已还' end
(
select uid,bid,tm from t where flag='借'
) a inner join (
select uid,bid,tm from t where flag='借'
) on a.uid=b.uid and a.bid=b.bid

[txlicenhe]

up

[takecare]

我试试看，谢谢先。