分组求和统计，with rollup 、with cube、grouping 统计函数用法
CREATE TABLE [kc1] ( [djrq] [smalldatetime] NULL , [djlx] [nvarchar] (20), [kfmc] [nvarchar] (50), [hpmc] [nvarchar] (50), [zl] [float] NULL ) GO insert into kc1 select '2010-03-20','初始库存','半成品','玉米','11' insert into kc1 select '2010-03-20','初始库存','半成品','豆粕','22' insert into kc1 select '2010-03-20','初始库存','半成品','鱼粉','33' insert into kc1 select '2010-03-21','初始库存','原料','玉米','1' insert into kc1 select '2010-03-21','初始库存','原料','豆粕','2' insert into kc1 select '2010-03-22','原种入库','半成品','玉米','11' insert into kc1 select '2010-03-22','原种入库','半成品','豆粕','22' insert into kc1 select '2010-03-22','种子入库','半成品','猪预混料','6' insert into kc1 select '2010-03-25','原种入库','预混料','猪预混料','2' insert into kc1 select '2010-03-26','原种出库','半成品','豆粕','-6' insert into kc1 select '2010-03-26','原种入库','销售仓库','玉米','11' insert into kc1 select '2010-03-26','种子出库','半成品','猪预混料','-1' insert into kc1 select '2010-03-26','原种入库','原料','豆粕','5' insert into kc1 select '2010-03-26','原种出库','原料','豆粕','-1' /* 求2个时间段：2010-03-20至2010-03-26 要得到这样的报表： 库房名称 起始时间 入库 出库 结余数量 kfmc djrq zl zl zl 半成品 2010-03-20 39 -7 98 销售仓库 2010-03-20 11 0 11 预混料 2010-03-20 2 0 2 原 料 2010-03-20 5 -1 7 */
备注：根据输入的2个时间参数，按照库房名称分组求和
入库方法：
- SQL code
- SELECT kfmc, SUM(zl)AS 入库 FROM kc1 where djlx like '%入库%' group by kfmc
出库方法：
- SQL code
- SELECT kfmc, SUM(zl)AS 出库 FROM kc1 where djlx like '%出库%' group by kfmc
- SQL code
- 结余数量: select kfmc,sum(zl) from kc1 group by kfmc
- CREATE TABLE [kc1] ( [djrq] [smalldatetime] NULL , [djlx] [nvarchar] (20), [kfmc] [nvarchar] (50), [hpmc] [nvarchar] (50), [zl] [float] NULL )GO insert into kc1 select '2010-03-20','初始库存','半成品','玉米','11' insert into kc1 select '2010-03-20','初始库存','半成品','豆粕','22' insert into kc1 select '2010-03-20','初始库存','半成品','鱼粉','33' insert into kc1 select '2010-03-21','初始库存','原料','玉米','1' insert into kc1 select '2010-03-21','初始库存','原料','豆粕','2' insert into kc1 select '2010-03-22','原种入库','半成品','玉米','11' insert into kc1 select '2010-03-22','原种入库','半成品','豆粕','22' insert into kc1 select '2010-03-22','种子入库','半成品','猪预混料','6' insert into kc1 select '2010-03-25','原种入库','预混料','猪预混料','2' insert into kc1 select '2010-03-26','原种出库','半成品','豆粕','-6' insert into kc1 select '2010-03-26','原种入库','销售仓库','玉米','11' insert into kc1 select '2010-03-26','种子出库','半成品','猪预混料','-1' insert into kc1 select '2010-03-26','原种入库','原料','豆粕','5' insert into kc1 select '2010-03-26','原种出库','原料','豆粕','-1' select [kfmc],min([djrq]), sum(case when charindex('入库',djlx)>0 then zl else 0 end) '入库', sum(case when charindex('出库',djlx)>0 then zl else 0 end) '出库', sum(zl) zl from kc1 group by [kfmc] kfmc 入库 出库 zl -------------------------------------------------- ----------------------- ---------------------- ---------------------- ---------------------- 半成品 2010-03-20 00:00:00 39 -7 98 销售仓库 2010-03-26 00:00:00 11 0 11 预混料 2010-03-25 00:00:00 2 0 2 原料 2010-03-21 00:00:00 5 -1 7 (4 行受影响)
select isnull([kfmc],'总计') [kfmc],min([djrq]), sum(case when djlx like '%入库%' then zl else 0 end) '入库', sum(case when djlx like '%出库%' then zl else 0 end) '出库', sum(zl) zl from kc1 where [djrq] between '2010-03-20' and '2010-03-26' group by [kfmc] with rollup kfmc 入库 出库 zl -------------------------------------------------- ----------------------- ---------------------- ---------------------- ---------------------- 半成品 2010-03-20 00:00:00 39 -7 98 销售仓库 2010-03-26 00:00:00 11 0 11 预混料 2010-03-25 00:00:00 2 0 2 原料 2010-03-21 00:00:00 5 -1 7 总计 2010-03-20 00:00:00 57 -8 118 (5 行受影响)
