一句统计sql语句的书写

[深圳phper]

现在在做一个统计报表，需要不同sku不同仓库下的 每天 期初（前一天的期末）与期末（当天期初+当天入库-当天出库）
表结构

CREATE TABLE `finance_day_invoicing` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `reference_date` date DEFAULT '0000-00-00' COMMENT '日期',
  `sku` varchar(64) DEFAULT NULL,
  `warehouseid` int(10) DEFAULT '0' COMMENT '仓库Id',
  `iqs` int(10) DEFAULT '0' COMMENT '期初数量',
  `ics` decimal(20,5) DEFAULT '0.00000' COMMENT '期初成本',
  `wq10` int(10) DEFAULT '0' COMMENT '采购入库数量',
  `wc10` decimal(20,5) DEFAULT '0.00000' COMMENT '采购入库金额',
  `dq10` int(10) DEFAULT '0' COMMENT '销售出库数量',
  `dc10` decimal(20,5) DEFAULT '0.00000' COMMENT '销售出库成本',
  `iqe` int(10) DEFAULT '0' COMMENT '期末库存数量',
  `ice` decimal(20,5) DEFAULT '0.00000' COMMENT '期末库存成本',
  PRIMARY KEY (`id`),
  KEY `IDX_DATE` (`reference_date`),
  KEY `IDX_SKU` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8 COMMENT='日结销存报表';

需要更新 每条（除第一条记录）记录 sku，仓库id，日期的
期初数量 iqs（当前sku，仓库id，日期以前的该sku数量之和）
及期初成本 ics（当前sku，仓库id，日期以前的该sku成本之和）
和期末数量（当前sku，仓库id，日期下 期初数量+入库数量-出库数量）
及期末成本（当前sku，仓库id，日期下 期初金额+入库金额-出库金额）
（第一条数据有默认值）
源数据sql语句

CREATE TABLE `finance_day_invoicing` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `reference_date` date DEFAULT '0000-00-00' COMMENT '日期',
  `sku` varchar(64) DEFAULT NULL,
  `warehouseid` int(10) DEFAULT '0' COMMENT '仓库Id',
  `iqs` int(10) DEFAULT '0' COMMENT '期初数量',
  `ics` decimal(20,5) DEFAULT '0.00000' COMMENT '期初成本',
  `wq10` int(10) DEFAULT '0' COMMENT '采购入库数量',
  `wc10` decimal(20,5) DEFAULT '0.00000' COMMENT '采购入库金额',
  `dq10` int(10) DEFAULT '0' COMMENT '销售出库数量',
  `dc10` decimal(20,5) DEFAULT '0.00000' COMMENT '销售出库成本',
  `iqe` int(10) DEFAULT '0' COMMENT '期末库存数量',
  `ice` decimal(20,5) DEFAULT '0.00000' COMMENT '期末库存成本',
  PRIMARY KEY (`id`),
  KEY `IDX_DATE` (`reference_date`),
  KEY `IDX_SKU` (`sku`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='日结销存报表';

INSERT INTO `finance_day_invoicing` VALUES ('1', '2018-01-09', 'ABC123', '1', '220', '12320.00000', '0', '0.00000', '26', '1456.25090', '0', '0.00000');
INSERT INTO `finance_day_invoicing` VALUES ('2', '2018-01-10', 'ABC123', '1', '0', '0.00000', '0', '0.00000', '3', '168.02895', '0', '0.00000');
INSERT INTO `finance_day_invoicing` VALUES ('3', '2018-01-11', 'ABC123', '1', '0', '0.00000', '0', '0.00000', '5', '280.04825', '0', '0.00000');
INSERT INTO `finance_day_invoicing` VALUES ('23', '2018-01-09', 'DEF123', '1', '100', '1500.00000', '0', '0.00000', '1', '15.56479', '0', '0.00000');
INSERT INTO `finance_day_invoicing` VALUES ('24', '2018-01-10', 'DEF123', '1', '0', '0.00000', '0', '0.00000', '1', '15.56479', '0', '0.00000');

[深圳phper]

想咨询下 if里面(if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date)还可以接 条件吗？如 AND reference_date>'2018-01-01'

引用 1 楼 zjcxc 的回复:

UPDATE finance_day_invoicing a, (
	SELECT id,
		@qs:=if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date, @qs, iqs) as iqs,
		@cs:=if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date, @cs, ics) as ics,
		@qs:=@qs+wq10-dq10 as iqe,
		@cs:=@cs+wc10-dc10 as ice,
		@sku:=sku, @warehouseid:=warehouseid, @reference_date:=reference_date
	FROM finance_day_invoicing
		,( SELECT @sku:=null, @warehouseid:=null, @reference_date:=null ) _r
	ORDER BY sku, warehouseid, reference_date
) b
set a.iqs=b.iqs, a.ics=b.ics, a.iqe=b.iqe, a.ice=b.ice
where a.id=b.id
;

[zjcxc]

加条件是肯定可以的

[zjcxc]

UPDATE finance_day_invoicing a, (
	SELECT id,
		@qs:=if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date, @qs, iqs) as iqs,
		@cs:=if(@sku<=>sku and @warehouseid<=>warehouseid and @reference_date<reference_date, @cs, ics) as ics,
		@qs:=@qs+wq10-dq10 as iqe,
		@cs:=@cs+wc10-dc10 as ice,
		@sku:=sku, @warehouseid:=warehouseid, @reference_date:=reference_date
	FROM finance_day_invoicing
		,( SELECT @sku:=null, @warehouseid:=null, @reference_date:=null ) _r
	ORDER BY sku, warehouseid, reference_date
) b
set a.iqs=b.iqs, a.ics=b.ics, a.iqe=b.iqe, a.ice=b.ice
where a.id=b.id
;

[Main_csdn_String]

- - 忽然知道为什么了。我的分太少了。没人答题。。。老天爷的