由简到难生成数据库报表（三）

           本文是此案例的收尾工作，再介绍一些零散的东西，也是比较常用的知识！

为查询编号

           要求按照主键排序，检索所有制单人不为空的销售单，并且为每行显示一个行号。 
  在MSSQLServer、 Oracle、 DB2等支持窗口函数的DBMS中， 使用窗口函数ROW_NUMBER()
可以完成这个功能： 

select row_number() over(order by fid) as rn,fnumber,FMakeDatefrom T_SaleBillwhere FMakeDate is not null

  对于MYSQL以及MSSQLServer2000等不支持窗口函数的DBMS函数可以使用子查询来完成
这个功能： 

SELECT  (   SELECT COUNT(*) FROM T_SaleBill t1   WHERE t1.FId<=t2.FId     AND t1.FBillMakerId IS NOT NULL ) AS rn, t2.FNumber,t2.FMakeDate FROM T_SaleBill t2 WHERE t2.FBillMakerId IS NOT NULL ORDER BY t2.FId 

  由于是按照FId排序， 而且FId的值是唯一的，所以使用相关子查询计算小于等于当前FId
值的行的个数就可以得到当前行的行号。 执行完毕我们就能在输出结果中看到上面的执行结
果

标记所有单内的最大销售量

要求将每张销售单中销售量最大的明细记录标记出来。 
  尝试使用下面的SQL语句来来完成要求的功能：

sELECT FId,FBillId,FMerchandiseId,FCount, 
CASE  
  WHEN FCount=MAX(FCount) 
  THEN '单内最大值' 
  ELSE ''  
END 
FROM T_SaleBillDetail 
GROUP BY FBillId 

  在这个 SQL 语句中，首先按照 FBillId 进行分组，然后使用聚合函数 MAX()来计算组内
FCount的最大值，最后使用CASE函数判断每一行的FCount是否等于这个最大值。 
  执行这个SQL语句后DBMS会报出如下的错误信息： 
选择列表中的列  'T_SaleBillDetail.FId' 无效，因为该列没有包含在聚合函数或  GROUP BY 子句中。 
  出现这个错误的原因是因为出现在 SELECT 列表中的所有列如果不是在聚合函数中使用则必须加入 GROUP  BY 子
句中。为了保证这个 SQL 语句能够正确运行，需要将用到的所有列放到GROUP BY子句中，SQL 语句如下：

select fid,fBillId,FMerchandiseId,Fcount,casewhen fcount=max(fcount)then '单内最大销售量'else ''endfrom t_SaleBillDetailgroup by fBillId,fid,FMerchandiseId,Fcount

 

  虽然 SQL 语句能够执行通过了，不过非常遗憾的是，这个执行结果是错误的，因为将
SELECT列表中的所有列都放到GROUP BY 子句中会破坏原有的分组。这里将讲解使用聚
合函数而又不必将SELECT列表中的所有列都放到GROUP BY 子句中的技巧。 
  在介绍窗口函数的时候曾经提到，使用窗口函数将无需使用 GROUP BY 子句，而且窗
口函数中的聚合计算不会影响其他的列，因此对于支持窗口函数的 DBMS 可以使用如下的
SQL语句： 

select fid,fBillId,FMerchandiseId,Fcount,casewhen fcount=max(fcount) over(partition by fbillId)then '单内最大销售量'else ''endfrom t_SaleBillDetailgroup by fBillId,fid,FMerchandiseId,Fcount

  这里使用窗口函数“MAX(FCount) OVER(PARTITION BY FBillId)”计算每一行所属
的销售单中的销售量的最大值，然后将其与 FCount 进行比较，如果等于 FCount 则表示当前
行是销售量的最大值所在的行。 
  执行完毕我们就能在输出结果中看到上面的执行结果： 

对于 MYSQL、MSSQLServer2000 等不支持窗口函数的 DBMS 来说，可以使用相关子

查询来达到相同的效果。SQL语句如下： 

select t1.fid,t1.fBillId,t1.FMerchandiseId,t1.fCount,casewhen fcount=(select max(Fcount) from T_SaleBillDetail t2where t2.fBillId=t1.fBillId)then '单内销售量最大'else ''end from T_SaleBillDetail t1

  这里使用相关子查询来计算每一个销售单中的销售量的最大值，其余部分与使用窗口函数
是一样的。需要注意的是相关子查询中的 WHERE 子句中将 t1.FBillId和 t2.FBillId进行
了相等性过滤，这样就达到了窗口函数中“PARTITION  BY  FBillId”一样的分区计算最大值
的效果，因此这个WHERE语句是不能遗漏的。 
  这个案例是非常典型的，当需要使用聚合计算，但是又不希望由于引入聚合函数而需要添
加额外的 GROUP BY 子句的话可以使用这里介绍的方案，那就是：支持窗口函数的 DBMS 使用
窗口函数，不支持窗口函数的DBMS使用子查询。 

总结：到目前为止，这个案例就基本上讲解完毕了。还有一些比较常用的功能就是关于日期、排序等

方面功能，大家可以了解一下这方面的函数！

我看过，现在网上流传什么SQL手册，我们要查的很多东西，这个手册都不能满足，

所以，我建议我们应该多去官网，看相应的函数介绍及帮助。

http://msdn.microsoft.com/en-us/sqlserver/default