mssql sqlserver中表数据查询时,在表行下面加入合计的方法分享


摘要:
下文讲述在查询表数据下面加入数值字段合计行数据,如下所示:
实验环境:sqlserver 2008 R2


例:

  ---文中存在一个多表查询的列数据,需在表下面加入合计行:
  select  a.billNo as [单号],a.custName as [客户名],b.qty as [数量],b.priceAll as [金额]  from busiMain a 
    left join busiDetail b on a.billNo =b.billNo 
 
 ---我们可以union all 方法在行数据后面加入统计行
   
   select  a.billNo as [单号],a.custName as [客户名],b.qty as [数量],b.priceAll as [金额]  from busiMain a 
    left join busiDetail b on a.billNo =b.billNo 
   union all   
  select  '合计','',u.[数量],u.[金额]from 
   ( select   sum(b.qty) as [数量],sum(b.priceAll) as [金额]  from busiMain a 
    left join busiDetail b on a.billNo =b.billNo ) as u