SQL 报表 --简易进销系统-白红宇

SQL 报表 --简易进销系统

阅读量：5077 次

发布时间：2019-06-12

本文共 11464 字，大约阅读时间需要 38 分钟。

模型图：

--  ============================================--  Author:    lifu--  Create Date: 2017-06-18--  Descrpition: 简化进销系统  报表制作--  ============================================--  Tables And Data Start ======================CREATE DATABASE SimpleEntrySystem    GOUSE SimpleEntrySystemGO    --创建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 detailGROUP BY  [detail].[FName]

转载于:https://www.cnblogs.com/GaoAnLee/p/7045254.html

你可能感兴趣的文章

bzoj 2456: mode【瞎搞】

查看>>

[Typescript] Specify Exact Values with TypeScript’s Literal Types

查看>>

[GraphQL] Reuse Query Fields with GraphQL Fragments

查看>>

Illustrated C#学习笔记（一）

查看>>

理解oracle中连接和会话

查看>>