--图书管理系统（借书、还书、书、库存、书类、学生）
--选课系统（学生、老师、选课、课程、班级、年级）
USE 坚持
--1、数据库设计（命名规范、主外键）
--2、某班所有学生二月份借的书目的总本数（相同的书不重复计算）(学号、借书本数)
SELECT A.stuID ,COUNT(B.BID)--注意and句在哪里
FROM student A
LEFT JOIN borrow B ON B.stuID=A.stuID AND DATEPART(MONTH,T_TIME)=12
GROUP BY A.stuID
SELECT A.stuID ,塞缪尔约翰逊COUNT(B.BID)
FROM borrow B
RIGHT JOIN student A ON B.stuID=A.stuID
and DATEPART(MONTH,T_TIME)=12
GROUP BY A.stuID
--3、某班所有学生最后借的一本书的借书信息（学号、书名、借书时间）
SELECT A.stuID,C.title,B.T_time
FROM student A
JOIN borrow B ON A.stuID=B.stuID
JOIN book C ON C.Bid=B.BID
WHERE B.T_time=(SELECT TOP 1 B.T_time
FROM borrow B
JOIN student A ON B.stuID=A.stuID
WHERE A.sclanum ='二班'
ORDER BY除号的由来 T_time DESC
)
--5、某位老师所教课程的所有学生的借书信息。(学号、书名、借书日期)
SELECT C.title ,B.T_time ,A.stuID
FROM student A
LEFT JOIN borrow B ON A.stuID =B.stuID
JOIN book C ON B.BID=C.Bid
WHERE A.stuID IN(SELECT D.sStudentid
FROM Elective D
JOIN cour E ON D.scourname =E.scourname
WHERE E.SSTAFFNUMBER ='')
--SELECT *FROM cour
--SELECT *fROM teacher
--SELECT *fROM
--ALTER TABLE COURSE
--ADD SSTAFFNUMBER VARCHAR(10)
--INSERT INTO teacher ()
--6、某班每位学生借的书名串联(学号、书名串联)
declare @STUID varchar(100)
declare @TITLE varchar(100)
declare @str varchar(1000)
declare @t table
(
stuID varchar(100)
,TITLE varchar(100)
)
午餐食谱declare curTemp Cursor
联想ceofor
SELECT A.stuID ,C.title
FROM student A
LEFT JOIN borrow B ON A.stuID=B.stuID
LEFT JOIN book 投胎C ON B.BID=C.Bid
WHERE A.sclanum ='二班'
group by A.stuID,C.title
open curTemp
fetch next from curTemp into @STUID,@TITLE
while @@fetch_status=0
begin
t @str=IsNull(@str+',','')+RTRIM(@TITLE)
fetch next from curTemp into @STUID,@TITLE
end
inrt into @t(stuID,TITLE)
values(@STUID,@str)
clo curTemp--关闭游标
deallocate curTemp--销毁游标
lect * from @t
--7、每个学生借的最贵的一本书的信息（学号、书名）
lect student.stuID,TITLE=(
lect top 1 BOOK.title
百货商店的英文
from borrow
join BOOK on食品安全案例 borrow.BID =book.Bid
where student.stuID=borrow.stuID
order by book.price DESC )
from student
order by student.stuID
--2、某班所有学生二月份借的书目的总本数（相同的书不重复计算）(学号、借书本数)
SELECT B.stuID,COUNT(DISTINCT A.Bid)----------结果正确
FROM student B
left JOIN borrow A ON B.stuID=A.stuID
AND DATEDIFF(MONTH,T_time,'2007-12-19')=0
GROUP BY B.stuID
SELECT *FROM student
SELECT *FROM borrow
SELECT *FROM book
SELECT百合花的图片 student.stuID, COUNT(DISTINCT BOOK.BID)--结果正确
FROM borrow
RIGHT JOIN student ON student.stuID=borrow.stuID
LEFT JOIN BOOK ON book.Bid=borrow.BID
AND DATEDIFF(MONTH,T_time,'2007-12-19')=0
GROUP BY student.stuID
--查询目前借书但未归还图书的学生名称及未还图书数量
lect 学生名称=(lect stuName from student where stuID=borrow.stuID),
借书数量=count(*) from borrow where B_time is null group by stuID
