这个是sql语句 select a.班级名称,count( a.班级ID) from 班级借阅表 b left join 班级表 a on a.班级id=b.班级ID left join 借阅表 c on b.用户id = c. 用户id where c.借阅时间 你输入的时间参数 你试试吧,我在网吧,就这么打的,应该有错误
ALTER PROCEDURE GetCalssBorrowCountByTime
( @TimeOne datetime, @TimeTwo datetime ) AS SELECT UserID, COUNT(*) AS BorrowCount into #tmp_class FROM dbo.TBL_Borrowinfo WHERE (UserID IN (SELECT DISTINCT UserID FROM dbo.TBL_Borrowinfo)) AND (BorrowDate BETWEEN @TimeOne AND @TimeTwo) GROUP BY UserID select c.ClassName,sum(a.BorrowCount) as ClassBorrowCount from #tmp_class a inner join TBL_User b on a.UserID=b.UserID inner join TBL_UserClass c on b.ClassID=c.ClassID GROUP BY c.ClassName drop table #tmp_class
借阅ID 书名 用户ID(借阅者) 借阅时间 还书时间 是否已归还(True为已归还)
17 6666 2007000008 2009-10-26 22:08:40 2009-10-27 10:10:08 True
20 123 2007000008 2009-10-27 10:07:58 2009-10-27 10:09:13 true
21 6666 2007000008 2009-10-27 10:10:08 NULL False
22 123 2007000008 2009-11-24 9:36:02 2009-12-5 20:13:08 True
23 9999 2007000008 2009-11-24 9:36:02 NULL False
25 7777 2007000008 2009-12-4 23:20:46 2009-12-5 0:54:04 True
28 7777 2007000008 2009-12-5 0:54:15 2009-12-5 21:09:54 True
29 123 888888 2009-12-5 20:13:30 NULL False
30 7777 123 2009-12-5 21:10:58 NULL False
32 2222 222 2009-12-5 21:15:41 NULL False
用户ID(借阅者) 班级ID
2007000008 1
888888 2
123 2
222 2
555 3班级ID 班级名称
1 1班
2 2班
3 3班想要显示的结果:一段时间内(12月份),每个班借阅次数班级名称 借阅次数
1班 2
2班 3
3班 0
select a.班级名称,count( a.班级ID) from 班级借阅表 b left join 班级表 a on a.班级id=b.班级ID left join 借阅表 c on b.用户id = c. 用户id where c.借阅时间 你输入的时间参数
你试试吧,我在网吧,就这么打的,应该有错误
(
@TimeOne datetime,
@TimeTwo datetime
)
AS
SELECT UserID, COUNT(*) AS BorrowCount into #tmp_class
FROM dbo.TBL_Borrowinfo
WHERE (UserID IN
(SELECT DISTINCT UserID
FROM dbo.TBL_Borrowinfo)) AND (BorrowDate BETWEEN @TimeOne AND @TimeTwo)
GROUP BY UserID select c.ClassName,sum(a.BorrowCount) as ClassBorrowCount from #tmp_class a inner join TBL_User b
on a.UserID=b.UserID
inner join TBL_UserClass c
on b.ClassID=c.ClassID
GROUP BY c.ClassName
drop table #tmp_class