建表
TA , 藏书表: 书ID,书号,书名,ISBN号,书价值,出版社,册数,复本量
TB , 读者表: 读者ID, 读者姓名,借书证号
TC , 借阅表:序号,日期,读者ID,书ID,归还状态(0未归还,1已归还)
h1: SELECT TA。
[书号],TA。[书名],COUNT(1) AS '借出本数'
FROM TA ,TC
WHERE TA。[书ID] = TC。[书ID]
GROUP BY TA。[书号],TA。[书名]
ORDER BY 3 DESC
h2: SELECT SUM([册数]) AS '总册数'
FROM TA
h3: SELECT SUM([册数] * [书价值]) AS '总值金额'
FROM TA
h4: SELECT TA。
[出版社],COUNT(DISTINCT(TA。[书ID]))
FROM TC,TA
WHERE TA。[书ID] = TC。[书ID] AND TC。[归还状态] = 0
GROUP BY TA。[出版社]
h5: SELECT TOP 1 TB。
[借书证号],TB。[姓名],SUM(TA。[书价值]) AS 总价值
FROM TA,TB,TC
WHERE TA。[书ID] = TC。[书ID] AND TB。[读者ID] = TC。[读者ID]
GROUP BY TB。
[借书证号],TB。[姓名]
ORDER BY 3 DESC
注:此处TOP N 可以修改,意义为按总值金额降序拍前N位
h6: 如有单独库存表可用库存表数值,如无库存表,库存取得用:藏书表册数 减去 已借出未归还数;
SELECT TA。
[ISBN号],TA。[书名]
FROM TA,(SELECT TC。[书ID],COUNT(1) AS '未还数' FROM TC WHERE [归还状态] = 0 GROUP BY TC。[书ID]) TX
WHERE TA。
[书ID] = TX。[书ID]
AND TA。[册数] - TX。[未还数] < TA。[复本量]
AND TA。[书ID] NOT EXISTS (SELECT 1 FROM TC WHERE TA。[书ID] = TC。
[书ID])
或
SELECT TA。[ISBN号],TA。[书名]
FROM TA
WHERE TA。[册数] - TX。[未还数] < TA。[复本量]
AND TA。[书ID] NOT EXISTS (SELECT 1 FROM TC WHERE TA。
[书ID] = TC。[书ID]) 。
全部