1. 在数据库中设计图书管理系统，成绩管理系统（两个系统设计成一个系统）。
2. 某班所有学生二月份借的书目的总本数（相同的书不重复计算）--
3. 某班所有学生最后借的一本书的借书信息（书名、时间）--
4. 本月借书信息、本年借书信息、上年借书信息、上年本月借书信息、上月借书信息（所有的都是求本数）
5. 某位老师所教课程的所有学生的借书信息---
6. 每位学生借的书名串联---
7. 每个学生借的最贵的一本书的信息--、
答案如下：如有不正确的地方，还请指正。
---第二题某班所有学生二月份借的书目的总本数（相同的书不重复计算）--
SELECT b.sStudentId AS '学生ID',
COUNT(DISTINCT c.sBookId) AS '数目总本数'
FROM BookInformation c
JOIN Borrow a ON c.sBookId=a.sBookId
JOIN student b ON a.sStudentId=b.sStudentId
JOIN class d ON b.iClassNo=d.iClassNO
WHERE DATEPART(MONTH,tBorrowTime)=2
AND d.iClassNO='307' GROUP BY b.sStudentId
---第三题某班所有学生最后借的一本书的借书信息（书名、时间）--
SELECT b.sStudentName, sBookName AS 书名 ,tBorrowTime AS 借书时间
FROM Borrow a
JOIN student b ON a.sStudentId=b.sStudentId
JOIN BookInformation c ON a.sBookId=c.sBookId
JOIN class d ON b.iClassNo=d.iClassNO
WHERE d.sClassName='07计算机网络'
AND a.sBookId =(SELECT top 1 a1.sBookId FROM Borrow a1
JOIN BookInformation e ON a1.sBookId=e.sBookId
WHERE a1.sStudentId=a.sStudentId
ORDER BY a1.tBorrowTime DESC)
---第四题本月借书信息、本年借书信息、上年借书信息、
--上年本月借书信息、上月借书信息（所有的都是求本数）--
SELECT [学生ID]=a.sStudentId,
[本月借书数]=(SELECT COUNT(b.sBookId) FROM Borrow b
WHERE DATEDIFF(MM,tBorrowTime,GETDATE())=0 AND a.sStudentId=b.sStudentId),
[本年借书数]=(SELECT COUNT(b.sBookId) FROM Borrow b
WHERE DATEDIFF(YY,tBorrowTime,GETDATE())=0 AND a.sStudentId=b.sStudentId),
[上年借书数]=(SELECT COUNT(b.sBookId) FROM Borrow b
WHERE DATEDIFF(YY,tBorrowTime,GETDATE())=1 AND a.sStudentId=b.sStudentId),
[上年本月借书数]=(SELECT COUNT(b.sBookId) FROM Borrow b
WHERE DATEDIFF(MM,tBorrowTime,GETDATE())=12 AND a.sStudentId=b.sStudentId),
[上月借书数]=(SELECT COUNT(b.sBookId) FROM Borrow b
WHERE DATEDIFF(MM,tBorrowTime,GETDATE())=1 AND a.sStudentId=b.sStudentId)
FROM student a
GROUP BY a.sStudentId
---第五题某位老师所教课程的所有学生的借书信息---
Declare @start TABLE (sStudentId varchar(50),sBookName varchar(255))
Declare @sStudentId varchar(50)
Declare @sBookName varchar(255)
Declare plea cursor
FOR
SELECT DISTINCT B.sStudentId
FROM teacher C
JOIN course D ON C.sTeacherName=D.sTeacherName
JOIN choosecourse E ON D.sCourseName=E.sCourseName
JOIN student B ON E.sStudentId=B.sStudentId
JOIN Borrow A ON B.sStudentId=A.sStudentId
JOIN BookInformation F ON A.sBookId=F.sBookId
WHERE C.sTeacherName='liuxiaofang'
OPEN plea
FETCH NEXT FROM plea INTO @sStudentId
WHILE @@FETCH_STATUS=0
BEGIN
SET @sBookName=''
SELECT @sBookName=F.sBookName+','+@sBookName
FROM teacher C
JOIN course D ON C.sTeacherName=D.sTeacherName
JOIN choosecourse E ON D.sCourseName=E.sCourseName
JOIN student B ON E.sStudentId=B.sStudentId
JOIN Borrow A ON B.sStudentId=A.sStudentId
JOIN BookInformation F ON A.sBookId=F.sBookId
WHERE B.sStudentId=@sStudentId
insert into @start values(@sStudentId,@sBookName)
FETCH NEXT FROM plea INTO @sStudentId
END
CLOSE plea
DEALLOCATE plea
SELECT sStudentId AS '学生编号',sBookName AS '书名'
FROM @start
---第六题每位学生借的书名串联---
USE Tsglxt
GO
Declare @csTEMP TABLE (sStudentId varchar(255),sBookName varchar(255))
Declare come Cursor
for
SELECT DISTINCT b.sStudentId
FROM student b
JOIN Borrow a ON b.sStudentId=a.sStudentId
JOIN BookInformation c ON a.sBookId=c.sBookId
Declare @sStudentId varchar(255)
Declare @sBookName varchar(255)
Open come
fetch next from come into @sStudentId
while @@FETCH_STATUS=0
begin
SET @sBookName=''
SELECT @sBookName=C.sBookName+','+@sBookName
FROM student b
JOIN Borrow a ON b.sStudentId=a.sStudentId
JOIN BookInformation c ON a.sBookId=c.sBookId
WHERE b.sStudentId=@sStudentId
insert into @csTEMP values(@sStudentId,@sBookName )
fetch next from come into @sStudentId
end
close come
deallocate come
SELECT sStudentId AS '学生ID',sBookName AS '书名'
FROM @csTEMP
---第七题每个学生借的最贵的一本书的信息--
SELECT A.sStudentId AS '学生Id',
C.sBookId AS '书ID',C.iBookPrice AS '书价格',
C.sBookName AS '书名'
FROM student A
JOIN Borrow B ON A.sStudentId=B.sStudentId
JOIN BookInformation C ON B.sBookId=C.sBookId
WHERE C.sBookId =(SELECT top 1 C1.sBookId FROM
BookInformation C1 JOIN Borrow B1 ON B1.sBookId=C1.sBookId
WHERE B1.sStudentId=B.sStudentId
order by iBookPrice DESC)
