sql数据库知识归纳：数据库大师成长日记

sql数据库知识归纳：数据库大师成长日记SaleBody(FHeadID int FBodyID int FProduct varchar(100) FQty decimal(28 10) FPrice decimal(28 10) FAmount decimal(28 10)) -- FHeadID - 与主表关联的单据内码 -- FBodyID - 单据分录的行标 -- FProduct - 销售产品名称 -- FQty - 销售数量 -- FPrice - 销售价格 -- FAmount - 销售金额 1001 1 芯片 1000 30 300001004 SO-1004 2018-11-14 华为SaleHead(FHeadID int FBillNo varchar(50) FDate smalldatetime FCSName varchar(255)) -- FHeadID - 单据内码，每张单据

朋友们，学习和应用SQL，主要的目的就是查询数据。SQL语言的精华，正是体现在对各种查询需求的支持。今天我们就说说查询时如何高效使用Group By分组。

Group By分组在SQL查询中应用是很广泛的，结合各种统计函数、Case子句，可以实现对数据的各种穿透组合，活学活用Group By，是书写高效SQL查询的基本技能。

举例用的表结构和示例数据

为了举例方便，我们先预设两张销售单据表，SaleHead为单据主表，每张单据一行；SaleBody为单据体表，保存单据的明细。

结构和初始数据如下：

SaleHead(FHeadID int FBillNo varchar(50) FDate smalldatetime FCSName varchar(255)) -- FHeadID - 单据内码，每张单据一个内码 -- FBillNo - 单据编号 -- FDate - 单据日期 -- FCSName - 客户名称

1001 SO-1001 2018-11-13 中兴

1002 SO-1002 2018-11-13 华为

1003 SO-1003 2018-11-14 华为

1004 SO-1004 2018-11-14 华为

SaleBody(FHeadID int FBodyID int FProduct varchar(100) FQty decimal(28 10) FPrice decimal(28 10) FAmount decimal(28 10)) -- FHeadID - 与主表关联的单据内码 -- FBodyID - 单据分录的行标 -- FProduct - 销售产品名称 -- FQty - 销售数量 -- FPrice - 销售价格 -- FAmount - 销售金额

1001 1 芯片 1000 30 30000

1001 2 配件 2000 10 20000

1002 1 芯片 3000 20 60000

1002 2 配件 3000 10 30000

1003 1 芯片 4000 20 80000

1004 1 芯片 3000 20 60000

假设共有四张单，第一张单据两行分录数据、第二张单据两行分录数据，第三、四张单一行分录数据。后面的统计以此数据为例。

Group By的基本应用

1、确定分组字段

分组字段是进行分组统计的基础，以销售表为例，我们分组的依据可以是分单据FHeadID或FBillNo、也可以是分客户FCSName、也可以是分日期FDate、也可以是分产品FProduct，当然也可以将以上分组字段组合起来使用。

2、确定统计字段

统计字段是分组统计的结果，一般是对数字进行统计，比如要统计单据张数、销售数量FQty汇总、销售金额FAmount汇总、销售数量FQty最多、销售价格FPrice最低最高等等。

3、常用统计函数

Count() - 个数统计

Sum() - 汇总统计

Avg() - 平均值统计

Max() - 最大值统计

Min() - 最小值统计

4、基本语法格式

select 部分分组字段 统计字段列表 from 表 where 条件 group by 分组字段列表 Having 条件 Group By的实际应用

1、根据单号统计每张单据的总数量、总金额

select b1.FBillNo as 单号 sum(b2.FQty) as 销售总量 sum(b2.FAmount) as 销售总额 from BillHead b1 inner join BillBody b2 on b1.FHeadID=b2.FHeadID group by b1.FBillNo

2、根据客户名称统计每个客户的销售总额

select b1.FCSName as 客户 sum(b2.FAmount) as 销售总额 from BillHead b1 inner join BillBody b2 on b1.FHeadID=b2.FHeadID group by b1.FCSName

3、根据单据日期统计每天的销售金额

select b1.FDate as 日期 sum(b2.FAmount) as 销售总额 from BillHead b1 inner join BillBody b2 on b1.FHeadID=b2.FHeadID group by b1.FDate

4、根据产品统计每种产品的销售数量、销售均价、销售金额

select b2.FProduct as 产品 sum(FQty) as 销售总量 sum(FAmount)/sum(FQty) as 均价 sum(FAmount) as 销售总额 from BillBody b2 group by b2.FProduct

5、统计每天的单据张数

select count(*) from BillHead group by FDate 或者也可以使用非Group By模式 select count(distinct FDate) from BillHead Group By使用Case When子句

case when 子句是分支结构，可以根据条件处理汇总，比如要统计每个客户11月13号之前的汇总和13号之后汇总，举例如下：

select b1.FCSName as 客户 sum(case when b1.FDate<='2018-11-13' then b2.FAmount else 0 end) as 之前总额 sum(case when b1.FDate<='2018-11-13' then 0 else b2.FAmount) as 之后金额 from BillHead b1 inner join BillBody b2 on b1.FHeadID=b2.FHeadID group by b1.FCSName Having子句的使用

Having子句在Group By之后，可以认为是对汇总后的数据进行二次筛选，要注意与Where子句的区别，比如要统计14号之后所有客户销售额大于100000的客户汇总，举例如下：

select b1.FCSName as 客户 sum(b2.FAmount) as 销售额 from BillHead b1 inner join BillBody b2 on b1.FHeadID=b2.FHeadID where FDate>'2018-11-14' group by b1.FCSName Having sum(b2.FAmount)>100000

可看出Where是对统计前的原始数据进行筛选，Having是对统计后的结果进行筛选。

朋友们，希望这些对您有所帮助。