三个sql 数据库表 图书表，图书类型表，借阅表之间的关联问题
SQL\u6570\u636e\u5e93\u91cc\u9762\u600e\u6837\u8bbe\u7f6e\u8868\u4e0e\u8868\u4e4b\u95f4\u7684\u5173\u8054\u8bbe\u7f6e\u5173\u8054\u7684\u4e24\u8868\u6216\u8005\u591a\u8868\u3001\u901a\u8fc7\u5916\u952e\u5173\u8054\u3001\u5916\u952e\u5b57\u6bb5\u662f\u4ece\u8868\u4e2d\u7684\u5b57\u6bb5\u3001\u5916\u952e\uff08FOREIGN KEY\uff09\u5b57\u6bb5\u9700\u8981\u5728\u4e3b\u8868\u505a\u4e3b\u952e(PRIMARY KEY)\u3001
\u6bd4\u5982
\u5b66\u751f\u8868(\u5b66\u53f7 \u4e3b\u952e\uff0c\u59d3\u540d) \u4e3b\u8868
\u8bfe\u7a0b\u8868(\u8bfe\u53f7 \u4e3b\u952e\uff0c\u8bfe\u540d) \u4e3b\u8868
\u9009\u4e86\u8868(\u5b66\u53f7 \u5916\u952e\uff0c \u8bfe\u53f7 \u5916\u952e\uff0c\u6210\u7ee9) \u4ece\u8868
\u8fd9\u6837\u4e09\u4e2a\u8868\u5c31\u5173\u8054\u4e86\u3001\u8bb0\u4f4f\u5efa\u8868\u987a\u5e8f\u3001\u5148\u4e3b\u8868\u3001\u518d\u4ece\u8868
\u7136\u540e\u53ef\u4ee5\u4f7f\u7528\u4ee3\u7801\u6216\u8005\u56fe\u5f62\u754c\u9762\u7ba1\u7406\u5668\u521b\u5efa\u5916\u952e\u3001\u521b\u5efa\u5b8c\u5c31\u5173\u8054\u4e86
\u5916\u952e\u7684\u53d6\u503c\u5fc5\u987b\u662f\u4e3b\u952e\u7684\u503c
\u540c\u5b66\u8981\u597d\u597d\u5b66\u4e60\u54e6!
CREATE TABLE [dbo].[\u4e66\u5e97\u8868] (
[\u4e66\u5e97\u7f16\u53f7] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u5e97\u540d] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u7535\u8bdd] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[\u5730\u5740] [char] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[\u90ae\u653f\u7f16\u7801] [char] (6) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[\u56fe\u4e66\u8868] (
[\u4e66\u53f7] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u4e66\u540d] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u7b2c\u4e00\u4f5c\u8005] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u51fa\u7248\u65e5\u671f] [smalldatetime] NULL ,
[\u4ef7\u683c] [numeric](4, 1) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[\u56fe\u4e66\u9500\u552e\u8868] (
[\u4e66\u53f7] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u4e66\u5e97\u7f16\u53f7] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[\u9500\u552e\u65e5\u671f] [smalldatetime] NOT NULL ,
[\u9500\u552e\u6570\u91cf] [smallint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[\u4e66\u5e97\u8868] WITH NOCHECK ADD
CONSTRAINT [PK_\u4e66\u5e97\u8868] PRIMARY KEY CLUSTERED
(
[\u4e66\u5e97\u7f16\u53f7]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[\u56fe\u4e66\u8868] WITH NOCHECK ADD
CONSTRAINT [PK_\u56fe\u4e66\u8868] PRIMARY KEY CLUSTERED
(
[\u4e66\u53f7]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[\u56fe\u4e66\u9500\u552e\u8868] WITH NOCHECK ADD
CONSTRAINT [PK_\u56fe\u4e66\u9500\u552e\u8868] PRIMARY KEY CLUSTERED
(
[\u4e66\u53f7],
[\u4e66\u5e97\u7f16\u53f7],
[\u9500\u552e\u65e5\u671f]
) ON [PRIMARY]
GO
FROM BookType INNER JOIN Book ON BookType.type_id = Book.type_id
GROUP BY BookType.type_id, BookType.type_name;
SELECT BookType.type_id, BookType.type_name, Count(Book.book_name) AS 当前借阅数量
FROM BookType INNER JOIN (Book INNER JOIN BorrowBooks ON Book.book_id = BorrowBooks.book_id) ON BookType.type_id = Book.type_id
GROUP BY BookType.type_id, BookType.type_name, BorrowBooks.IsReturned
HAVING (((BorrowBooks.IsReturned)=False));
SELECT BookType.type_id, BookType.type_name, Count(Book.book_name) AS 合计借出数量
FROM BookType INNER JOIN (Book INNER JOIN BorrowBooks ON Book.book_id = BorrowBooks.book_id) ON BookType.type_id = Book.type_id
GROUP BY BookType.type_id, BookType.type_name, BorrowBooks.IsReturned
HAVING (((BorrowBooks.IsReturned)=True));
分析：馆藏总量 就是代表图书馆里有多少本书（count(c.book_id)就可以统计出）
当前借阅数量：肯定统计的是 BorrowBooks 的借阅数量（count(c.book_id) ）
select b.type_id,b.type_name,count(a.book_id) as '馆藏总量', count(c.book_id) as '当前借阅数量' from Book as a,BookType as b ,BorrowBooks as c where a.type_id=b.type_id and b.type_id=c.type_id and a.type_id=c.type_id and c.IsReturned='否'
呵呵，没经过调试，你试哈，不行的话，在说哈
对图书借阅管理作简单的需求分析，建立实体关系模型，这个设计的 数据库， SQL语言， MS SQL Server
扩展阅读：sql server常用语句大全 ... 常用的四种sql增删改查 ... 数据库图书管理系统表 ... sql和python哪个难学 ... sql 查询大量数据 ... mysql图书借阅系统 ... sql 完整查询是什么 ... sql 循环查询 ... sqlplus查看数据库 所有表 ...
