模型图：
-- ============================================
-- Author: lifu
-- Create Date: 2017-06-18
-- Descrpition: 简化进销系统 报表制作
-- ============================================
-- Tables And Data Start ======================
CREATE DATABASE SimpleEntrySystem
GO
USE SimpleEntrySystem
GO
--创建T_Person表 人员表
CREATE TABLE T_Person
(
FId VARCHAR(20) NOT NULL ,
FNumber VARCHAR(20) , --人员工号
FName VARCHAR(20) , --人员姓名
FManagerId VARCHAR(20) , --上级主管主键（指向T_Person表的 FId字段的外键）
PRIMARY KEY ( FId ) ,
FOREIGN KEY ( FManagerId ) REFERENCES T_Person ( Fid )
)
--创建T_Merchandise表 商品表
CREATE TABLE T_Merchandise
(
FId VARCHAR(20) NOT NULL ,
FNumber VARCHAR(20) , --商品编号
FName VARCHAR(20) , --商品名
FPrice INT , --商品价格
PRIMARY KEY ( fid )
)
--创建T_SaleBill表 销售单主表
CREATE TABLE T_SaleBill
(
FId VARCHAR(20) NOT NULL ,
FNumber VARCHAR(20) , --销售单编号
FBillMakerId VARCHAR(20) ,--开单人主键（指向T_Person表的 FId字段的外键）
FMakeDate DATETIME , --制单日期
FConfirmDate DATETIME , --确认日期
PRIMARY KEY ( fid ) ,
FOREIGN KEY ( Fbillmakerid ) REFERENCES T_Person ( fid )
)
--创建T_SaleBillDetail表 销售单明细记录
CREATE TABLE T_SaleBillDetail
(
FId VARCHAR(20) ,
FBillId VARCHAR(20) , --主表主键（指向 T_SaleBill 表的 FId 字段的外键）
FMerchandiseId VARCHAR(20) , --商品主键（指向T_Merchandise表的FId字段的外键）
FCount INT , --销售数量
PRIMARY KEY ( fid ) ,
FOREIGN KEY ( Fbillid ) REFERENCES T_SaleBill ( fid ) ,
FOREIGN KEY ( Fmerchandiseid ) REFERENCES T_Merchandise ( fid )
)
--创建T_PurchaseBill表 采购单主表
CREATE TABLE T_PurchaseBill
(
Fid VARCHAR(20) NOT NULL ,
FNumber VARCHAR(20) , --采购单编号
FBillMakerId VARCHAR(20) ,--开单人主键（指向T_Person表的FId字段的外键）
FMakeDate DATETIME , --制单日期
FConfirmDate DATETIME , --确认日期
PRIMARY KEY ( fid ) ,
FOREIGN KEY ( FBillMakerId ) REFERENCES T_Person ( fid )
)
--创建T_PurchaseBillDetail表 采购单明细记录
CREATE TABLE T_PurchaseBillDetail
(
FId VARCHAR(20) NOT NULL ,
FBillId VARCHAR(20) , --主表主键（指向T_PurchaseBill表的FId字段的外键）
FMerchandiseId VARCHAR(20) ,--商品主键（指向T_Merchandise表的FId字段的外键）
FCount INT , --采购数量
PRIMARY KEY ( FId ) ,
FOREIGN KEY ( FBillId ) REFERENCES T_PurchaseBill ( FId ) ,
FOREIGN KEY ( FMerchandiseId ) REFERENCES T_Merchandise ( FId )
)
--首先向T_Person、T_Merchandise两张表中插入演示数据：
INSERT INTO T_Person ( FId, FNumber, FName, FManagerId )
VALUES ( '00001', '1', 'Robert', NULL )
INSERT INTO T_Person ( FId, FNumber, FName, FManagerId )
VALUES ( '00002', '2', 'John', '00001' )
INSERT INTO T_Person ( FId, FNumber, FName, FManagerId )
VALUES ( '00003', '3', 'Tom', '00001' )
INSERT INTO T_Person ( FId, FNumber, FName, FManagerId )
VALUES ( '00004', '4', 'Jim', '00003' )
INSERT INTO T_Person ( FId, FNumber, FName, FManagerId )
VALUES ( '00005', '5', 'Lily', '00002' )
INSERT INTO T_Person ( FId, FNumber, FName, FManagerId )
VALUES ( '00006', '6', 'Merry', '00003' )
INSERT INTO T_Merchandise ( FId, FNumber, FName, FPrice )
VALUES ( '00001', '1', 'Bacon', 30 )
INSERT INTO T_Merchandise ( FId, FNumber, FName, FPrice )
VALUES ( '00002', '2', 'Cake', 2 )
INSERT INTO T_Merchandise ( FId, FNumber, FName, FPrice )
VALUES ( '00003', '3', 'Apple', 6 )
-- 还要向T_SaleBill和T_PurchaseBill表中插入演示数据：
INSERT INTO T_SaleBill ( FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate )
VALUES ( '00001', '1', '00006', '2007-03-15', '2007-05-15' )
INSERT INTO T_SaleBill ( FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate )
VALUES ( '00002', '2', NULL, '2006-01-25', '2006-02-03' )
INSERT INTO T_SaleBill ( FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate )
VALUES ( '00003', '3', '00001', '2006-02-12', '2007-01-11' )
INSERT INTO T_SaleBill ( FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate )
VALUES ( '00004', '4', '00003', '2008-05-25', '2008-06-15' )
INSERT INTO T_SaleBill ( FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate )
VALUES ( '00005', '5', '00005', '2008-03-17', '2007-04-15' )
INSERT INTO T_SaleBill ( FId, FNumber, FBillMakerId, FMakeDate, FConfirmDate )
VALUES ( '00006', '6', '00002', '2002-02-03', '2007-11-11' )
INSERT INTO T_PurchaseBill ( FId, FNumber, FBillMakerId, FMakeDate,
FConfirmDate )
VALUES ( '00001', '1', '00006', '2007-02-15', '2007-02-15' )
INSERT INTO T_PurchaseBill ( FId, FNumber, FBillMakerId, FMakeDate,
FConfirmDate )
VALUES ( '00002', '2', '00004', '2003-02-25', '2006-03-03' )
INSERT INTO T_PurchaseBill ( FId, FNumber, FBillMakerId, FMakeDate,
FConfirmDate )
VALUES ( '00003', '3', '00001', '2007-02-12', '2007-07-12' )
INSERT INTO T_PurchaseBill ( FId, FNumber, FBillMakerId, FMakeDate,
FConfirmDate )
VALUES ( '00004', '4', '00002', '2007-05-25', '2007-06-15' )
INSERT INTO T_PurchaseBill ( FId, FNumber, FBillMakerId, FMakeDate,
FConfirmDate )
VALUES ( '00005', '5', '00002', '2007-03-17', '2007-04-15' )
INSERT INTO T_PurchaseBill ( FId, FNumber, FBillMakerId, FMakeDate,
FConfirmDate )
VALUES ( '00006', '6', NULL, '2006-02-03', '2006-11-20' )
-- 向T_SaleBillDetail表和T_PurchaseBillDetail表中插入演示数据：
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00001', '00001', '00003', 20 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00002', '00001', '00001', 30 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00003', '00001', '00002', 22 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00004', '00002', '00003', 12 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00005', '00002', '00002', 11 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00006', '00003', '00001', 60 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00007', '00003', '00002', 2 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00008', '00003', '00003', 5 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00009', '00004', '00001', 16 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00010', '00004', '00002', 8 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00011', '00004', '00003', 9 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00012', '00005', '00001', 6 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00013', '00005', '00003', 26 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00014', '00006', '00001', 66 )
INSERT INTO T_SaleBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00015', '00006', '00002', 518 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00001', '00001', '00002', 12 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00002', '00001', '00001', 20 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00003', '00002', '00001', 32 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00004', '00002', '00003', 18 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00005', '00002', '00002', 88 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00006', '00003', '00003', 19 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00007', '00003', '00002', 6 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00008', '00003', '00001', 2 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00009', '00004', '00001', 20 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00010', '00004', '00003', 18 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00011', '00005', '00002', 19 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00012', '00005', '00001', 26 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00013', '00006', '00003', 3 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00014', '00006', '00001', 22 )
INSERT INTO T_PurchaseBillDetail ( FId, FBillId, FMerchandiseId, FCount )
VALUES ( '00015', '00006', '00002', 168 )
-- Tables And Data End =======================
-- 报表===================================================================
USE [SimpleEntrySystem]
GO
-- 显示制单人详细信息
SELECT [FNumber], [FBillMakerId], [FMakeDate]
FROM [dbo].[T_SaleBill]
--inner join 需要知道是哪个人开的单
SELECT [s].[FNumber], [p].[FName], [s].[FMakeDate]
FROM [dbo].[T_SaleBill] AS [s]
INNER JOIN [dbo].[T_Person] AS [p] ON [s].[FBillMakerId] = [p].[FId]
--left outer join 需要将空的开单人标识
SELECT [s].[FNumber], COALESCE([p].[FName], '没有开单人'), [s].[FMakeDate]
FROM [dbo].[T_SaleBill] AS [s]
LEFT OUTER JOIN [dbo].[T_Person] AS [p] ON [s].[FBillMakerId] = [p].[FId]
-- 显示销售单的信息
/*
要求列出所有销售单的详细信息，每行显示销售单的每一条销售记录，同时每行头部要
显示此行所属的销售单的信息，比如单号、开单人、开单日期等。T_SaleBillDetail表保存的
是销售单的每一条销售记录，T_SaleBill表保存的是销售单的头信息，T_SaleBillDetail表的
FMerchandiseId字段保存的是销售的商品主键，而 T_SaleBill表的 FBillMakerId字段保存的
是开单人的主键，只要对这四张表做连接查询即可。由于 T_SaleBill表的 FBillMakerId字段
有可能为空，所以在 T_SaleBill 表和 T_Person 表进行连接的时候要使用左外连接，而为了
提高查询效率其他连接都使用内连接
*/
SELECT [saleBill].[FNumber] AS '销售单编号',
COALESCE([person].[FName], '没有开单人') AS '开单人',
[saleBill].[FMakeDate] AS '销售时间', [merchandise].[FName] AS '商品名称',
[saleBillDetail].[FCount] AS '销售数量'
FROM [dbo].[T_SaleBill] AS [saleBill]
LEFT OUTER JOIN [dbo].[T_Person] AS [person] ON [saleBill].[FBillMakerId] = [person].[FId]
INNER JOIN [dbo].[T_SaleBillDetail] AS [saleBillDetail] ON saleBillDetail.[FBillId] = [saleBill].[FId]
INNER JOIN [dbo].[T_Merchandise] AS [merchandise] ON [merchandise].[FId] = [saleBillDetail].[FMerchandiseId]
ORDER BY [saleBill].[FMakeDate] DESC
--收益计算
/*
要求计算每种商品的总收益， 受收益的定义为所有的销售单中该商品的销售总额减去所
有的采购单中该商品的购买总额。
*/
----------------------------------------------------------------------------------
--销售额
SELECT [m].[FName] AS '商品名称', [m].[FPrice] * [sbd].[FCount] AS '销售额'
FROM [dbo].[T_Merchandise] AS [m]
INNER JOIN [dbo].[T_SaleBillDetail] AS [sbd] ON [m].[FId] = [sbd].[FMerchandiseId]
UNION ALL
--采购额
SELECT [m].[FName] AS '商品名称', [m].[FPrice] * [pbd].[FCount] * ( -1 ) AS '采购额'
FROM [dbo].[T_Merchandise] AS [m]
INNER JOIN [dbo].[T_PurchaseBillDetail] AS [pbd] ON [m].[FId] = [pbd].[FMerchandiseId]
----------------------------------------------------------------------------------
--将采购和销售计算合并
SELECT [detail].[FName], SUM([detail].[总额])
FROM ( SELECT [m].[FName] ,
[m].[FPrice] * [sbd].[FCount] AS '总额'
FROM [dbo].[T_Merchandise] AS [m]
INNER JOIN [dbo].[T_SaleBillDetail] AS [sbd] ON [m].[FId] = [sbd].[FMerchandiseId]
UNION ALL
SELECT [m].[FName] ,
[m].[FPrice] * [pbd].[FCount] * ( -1 ) AS '总额'
FROM [dbo].[T_Merchandise] AS [m]
INNER JOIN [dbo].[T_PurchaseBillDetail] AS [pbd] ON [m].[FId] = [pbd].[FMerchandiseId]
) AS detail
GROUP BY [detail].[FName]