由简到难生成数据库报表(一)
在接下来,我想借助一个例子,来加深大家对数据库报表的认识,由简到难生成数据库报表!
这里创建一个简化的进销系统,系统中只有销售单和采购单,不存在红冲单据及其库存、退货等单据。由于销售单和采购单存在主从结构,所以将这两张表中的主从数据分别保存在不同的表中。下面是这个系统中表之间的关系图:
表 T_Person 为人员表,FId字段为主键,FNumber 字段为人员工号,FName 字段为人
员姓名,FManagerId字段为上级主管主键(指向T_Person表的 FId字段的外键) 。
表T_Merchandise为商品表,FId字段为主键,FNumber字段为产品编号,FName字段
为商品名,FPrice为商品价格;
表T_SaleBill为销售单主表,FNumber字段为销售单编号,
FBillMakerId字段为开单人主键(指向T_Person表的 FId字段的外键) ,FMakeDate 字段为
制单日期,FConfirmDate字段为确认日期;
表T_SaleBillDetail为销售单明细记录,FId字段
为主键,FBillId字段为主表主键(指向 T_SaleBill 表的 FId 字段的外键) ,FMerchandiseI
字段为商品主键(指向T_Merchandise表的FId字段的外键) ,FCount字段为销售数量。
表T_PurchaseBill为采购单主表,FNumber字段为采购单编号,FBillMakerId字段为开
单人主键(指向T_Person表的FId字段的外键), FMakeDate字段为制单日期, FConfirmDat
字段为确认日期;
表T_PurchaseBillDetail为采购单明细记录,FId字段为主键,FBillId字段
为主表主键(指向T_PurchaseBill表的FId字段的外键) , FMerchandiseId字段为商品主键(指
向T_Merchandise表的FId字段的外键),FCount字段为采购数量。
下面是创建表的SQL语句以及插入数据的SQL语句:
显示制单人详细信息
句来完成这个任务:
SELECT FNumber, FBillMakerId, FMakeDate
FROM T_SaleBill
号与人名对应起来,因此必须将其转换为制单人的姓名。FBillMakerId 字段保存的是
T_Person表的主键,而T_Person表的FName字段则为人员的名称,因此将这两个表做连接
查询即可,SQL语句如下:
FROM T_SaleBill salebill
INNER JOIN T_Person person
ON salebill.FBillMakerId=person.FId;
显示在执行结果中,这是因为这条记录的 FBillMakerId 字段为空值,所以不能与 T_Person
表中的任何记录进行匹配,而内连接不会显示没有匹配的行。一般情况下即使没有开单人也
要将这张单据显示出来,这时就要使用外部连接了,如下:
左外部连接。执行完毕我们就能在输出结果中看到上面的执行结果:
让业务人员感到难以理解,我们使用 COALESCE()函数来解决这个问题。前面章节讲到
COALESCE()函数支持多个参数, 该函数返回参数中的第一个非空值, 这样 COALESCE(f1,f2)
就可以实现“如果 f1为空则将 f2做为返回值”这样的空值处理逻辑了。将SQL语句做如下改
造:
显示销售单的信息
显示此行所属的销售单的信息,比如单号、开单人、开单日期等。T_SaleBillDetail表保存的
是销售单的每一条销售记录,T_SaleBill表保存的是销售单的头信息,T_SaleBillDetail表的
FMerchandiseId字段保存的是销售的商品主键,而 T_SaleBill表的 FBillMakerId字段保存的
是开单人的主键,只要对这四张表做连接查询即可。由于 T_SaleBill表的 FBillMakerId字段
有可能为空,所以在 T_SaleBill 表和 T_Person 表进行连接的时候要使用左外连接,而为了
提高查询效率其他连接都使用内连接。SQL语句如下:
计算收益
T_SaleBillDetail 表中保存的所有的销售单详细记录,因此下面的 SQL 语句可以检索
所有产品的销售记录,包括产品名和销售额:
就无法区分销售单和采购单了。为了区分销售单和采购单,同时方便后续运算,我们将检索
产品的购买记录的金额全部取负值,这样就可以表示采购行为的金额为负值:
为正值,而购买行为的交易额为负值。有个这个执行结果,只要将这个 SQL 语句做为子查
询,然后按照商品名进行分组,然后计算交易金额的总和。SQL语句如下: