27.案例：报表制作

1.显示销售单的详细信息

     要求列出所有销售单的详细信息，每行显示销售单的每一条销售记录，同时每行头部要显示此行所属的销售单的信息，比如单号、开单人、开单日期等。

SELECT 
    salebill.FNumber as 订单编号,person.FName as 开单人,
    salebill.FMakeDate as 制单日期,merchandise.FName as 商品名,
    salebilldetail.FCount as 商品数量
FROM
    T_SaleBill as saleBill 
INNER JOIN T_SaleBillDetail as salebilldetail ON salebilldetail.FBillId=saleBill.FId 
INNER JOIN T_Merchandise as merchandise ON salebilldetail.FMerchandiseId=merchandise.FId 
--没有开单人就为空,这里用左外连接
LEFT OUTER JOIN T_Person as person ON saleBill.FBillMakerId=person.FId 

2.计算收益

     要求计算每种商品的总收益，受收益的定义为所有的销售单中该商品的销售总额减去所有的采购单中该商品的购买总额。

     T_SaleBillDetail表中保存的所有的销售单详细记录，T_PurchaseBillDetail保存所有产品的购买记录。将二者UNION运算就可以将两个检索的结果集合并了，不过这样就无法区分销售单和采购单了。为了区分销售单和采购单，同时方便后续运算，我们将检索产品的购买记录的金额全部取负值，这样就可以表示采购行为的金额为负值：因为有可能存在重复的记录（即同一种商品有同样的交易额），所以要使用UNION ALL，以防止重复的记录被合并。

SELECT 
    merchandise.FName AS 商品名, 
    merchandise.FPrice*salebilldetail.FCount AS 统计
FROM T_SaleBillDetail salebilldetail 
    INNER JOIN T_Merchandise merchandise ON salebilldetail.FMerchandiseId=merchandise.FId 
    
UNION ALL 

SELECT
    merchandise.FName AS 商品名, 
    merchandise.FPrice*purchasebilldetail.FCount*(-1) AS 统计 
FROM T_PurchaseBillDetail purchasebilldetail 
    INNER JOIN T_Merchandise merchandise ON purchasebilldetail.FMerchandiseId=merchandise.FId 

                              

     这个结果集中列出了每一条详细交易记录，包括商品名和交易金额，销售行为的交易额为正值，而购买行为的交易额为负值。有个这个执行结果，只要将这个SQL语句做为子查询，然后按照商品名进行分组，然后计算交易金额的总和。

SELECT details.MerchandiseName as 商品名,SUM(details.Amount)  as 收入
FROM 
( 
    SELECT 
        merchandise.FName as MerchandiseName, 
        merchandise.FPrice*salebilldetail.FCount as Amount
    FROM T_SaleBillDetail salebilldetail 
        INNER JOIN T_Merchandise merchandise ON salebilldetail.FMerchandiseId=merchandise.FId 
        
    UNION ALL 

    SELECT
        merchandise.FName as MerchandiseName,  
        merchandise.FPrice*purchasebilldetail.FCount*(-1) as Amount
    FROM T_PurchaseBillDetail purchasebilldetail 
        INNER JOIN T_Merchandise merchandise ON purchasebilldetail.FMerchandiseId=merchandise.FId 
) details 
GROUP BY details.MerchandiseName

3.统计销售记录的份额 

     要求统计所有的销售明细，并且计算每一笔销售记录中销售量占同产品总销售量的百分比。

SELECT 
   merchandise.FName,
   FCount, 
   FCount*1.0/SUM(salebilldetail.FCount) OVER(PARTITION BY salebilldetail.FMerchandiseId)  as 份额 
FROM 
   T_SaleBillDetail as salebilldetail --销售细节表
   INNER JOIN T_Merchandise as merchandise ON salebilldetail.FMerchandiseId=merchandise.FId 

4.为采购单分级 

     统计每张采购单的单号、总采购额，并且对于总采购额小于等于500元的显示为“小额”、总采购额大于等于1000元的显示为“大额”、介于500元与1000元之间的显示为“普通”。

SELECT 
    t.BillNumber 订单编号,
    t.TotalAmount 总采购额, 
    CASE  
      WHEN t.TotalAmount <=500 THEN '小额' 
      WHEN t.TotalAmount >=1000 THEN '大额' 
      ELSE '普通' 
    END as 分级
FROM 
( 
    SELECT 
       purchasebill.FNumber AS BillNumber, 
       SUM(purchasebilldetail.FCount*merchandise.FPrice) AS TotalAmount
    FROM T_PurchaseBillDetail as purchasebilldetail 
    INNER JOIN T_PurchaseBill as purchasebill ON purchasebilldetail.FBillId = purchasebill.FId 
    INNER JOIN T_Merchandise as merchandise ON purchasebilldetail.FMerchandiseId = merchandise.FId 
    GROUP BY purchasebill.FNumber --根据订单编号来对明细表分组
) t 

5.检索所有重叠日期销售单

     求索所有重叠日期销售单。重叠日期销售单的定义如下：如果销售单t1 的开单日期介于销售单t2的开单日期和确认日期之间的话，我们就说t1与t2日期重叠。类似的需求在生产排程、工作计划安排等场景也可以看到。

     通过交叉连接获取可比较数据：

SELECT
    t1.FNumber as 订单编号1,t1.FMakeDate as 制单日期1,t1.FConfirmDate as 确认日期1, 
    t2.FNumber as 订单编号2,t2.FMakeDate as 制单日期2,t2.FConfirmDate as 确认日期2
FROM T_SaleBill t1,T_SaleBill t2 --交叉连接

SELECT
    t1.FNumber as 订单编号1,t1.FMakeDate as 制单日期1,t1.FConfirmDate as 确认日期1, 
    t2.FNumber as 订单编号2,t2.FMakeDate as 制单日期2,t2.FConfirmDate as 确认日期2
FROM T_SaleBill t1,T_SaleBill t2 --交叉连接
WHERE 
  t2.FMakeDate>=t1.FMakeDate  AND --t1的制单日期在t2的制单日期和确认日期之间,并且不是同一条订单记录
  t2.FMakeDate<=t1.FConfirmDate  AND 
  t1.FId<>t2.FId 

6.标记所有单内最大销售量

     使用窗口函数将无需使用GROUP BY子句，而且窗口函数中的聚合计算不会影响其他的列。这里使用窗口函数“MAX(FCount) OVER(PARTITION BY FBillId)”计算每一行所属的销售单中的销售量的最大值，然后将其与FCount进行比较，如果等于FCount则表示当前行是销售量的最大值所在的行。

SELECT 
    FId,FBillId,FMerchandiseId,FCount, 
    CASE  
      WHEN FCount=MAX(FCount) OVER(PARTITION BY FBillId) THEN '单内最大值' 
      ELSE ''  
    END as 单内最大值
FROM T_SaleBillDetail