mysql SQL 语句怎么将一个表中字段更新成大于当前时间该字段所有的和

[深圳phper]

demo表
字段 id, name ,cost(当日成本), total_cost(累计成本),mdate(日期)

现在想写个sql语句，将 total_cost 更新成 大于当前日期mdate的所有 cost的和

[深圳phper]

引用 1 楼 zjcxc 的回复:

update demo表 set total_cost=(select sum(cost) from(select * from demo表) b where b.mdate>demo表.mdate);

如果需要更新表中多个字段，如：需要更新：成本total_cost1 total_cost2,还有数量 total_num1,total_num2 有更高效的写法吗？

[听雨停了]

引用 2 楼 nowphp 的回复:

[quote=引用 1 楼 zjcxc 的回复:]

update demo表 set total_cost=(select sum(cost) from(select * from demo表) b where b.mdate>demo表.mdate);

如果需要更新表中多个字段，如：需要更新：成本total_cost1 total_cost2,还有数量 total_num1,total_num2 有更高效的写法吗？[/quote]

UPDATE demo表
SET    total_cost = (
           SELECT SUM(cost)
           FROM   (
                      SELECT *
                      FROM   demo表
                  ) b
           WHERE  b.mdate > demo表.mdate);
--先执行上面的再执行下面的           
UPDATE demo表 SET
total_cost1=total_cost,total_cost2=total_cost,total_num1=total_cost,total_num2=total_cost

[zjcxc]

如果你的所有字段都来自同一个累计值，那么很简单，把这些字段更新一次就行

update demo表 set total_cost=(select sum(cost) from(select * from demo表) b where b.mdate>demo表.mdate),
total_cost1=total_cos, total_cost2=total_cost, .....

[zjcxc]

如果你要更新的数据来自相同条件的不同字段累计，那么就先算出条记录的累计值，然后 join 更新（要求或多个一个能唯一标识一条记录的列）

-- 假设 id 能唯一标识一条记录
update demo表, (
	select id, @1 as total_cost1, @2 as total_cost2,
		@1:=@1+total_cost1,
		@2:=@2+total_cost12
	from demo表, (select @1:=0, @2:=0) x
	order by mdate
) b 
set total_cost1= b.total_cost1, total_cost2=b.total_cost2
where demo表.id = b.id

[深圳phper]

引用 5 楼 zjcxc 的回复:

如果你要更新的数据来自相同条件的不同字段累计，那么就先算出条记录的累计值，然后 join 更新（要求或多个一个能唯一标识一条记录的列）

-- 假设 id 能唯一标识一条记录
update demo表, (
	select id, @1 as total_cost1, @2 as total_cost2,
		@1:=@1+total_cost1,
		@2:=@2+total_cost12
	from demo表, (select @1:=0, @2:=0) x
	order by mdate
) b 
set total_cost1= b.total_cost1, total_cost2=b.total_cost2
where demo表.id = b.id

就是多个不同字段的和，没太看懂sql

[zjcxc]

那你举数据说明问题吧，再根据你举的数据来写

[zjcxc]

查询应该没什么难看懂的，UPDATE 很简单，应该不用解释 对于 子查询 B， 就是 order by mdate 查询 demo表， 对于每条记录，先输出变量 @1， @2 两个变量的值，然后这两个变量累加当前记录值 （这里偷懒没有判断 mdate, 也就是认为 mdate 不重复，所以当前记录累加前的值就是 < 当前记录 mdate 的累计值 ） 变量的初始化是用这个实现： , (select @1:=0, @2:=0) x

[深圳phper]

表 id ,sku, date ，start_total_cost, start_total_num, cost1, cost2, cost3, num1, num2, num3 end_total_cost, end_total_num date为日期 为每天日期 start_total_cost 为每天开始时候的库存成本（前一天end_total_cost） start_total_num 为每天开始时候的库存成本（前一天end_total_num） end_total_cost, 为当天 start_total _cost - cost1 - cost2 + cost3 end_total_num 为当天 start_total _ num - num1 - num2 + num3 现在每天数据 cost1 cost2 cost3 num1 num2 num3 都已经有了，现在需要根据每天的数据把每天的 start_total_cost, start_total_num, end_total_cost, end_total_num 给刷新jinqv

引用 8 楼 zjcxc 的回复:

大神帮忙看看

[zjcxc]

update demo表 set total_cost=(select sum(cost) from(select * from demo表) b where b.mdate>demo表.mdate);

[zjcxc]

UPDATE 表, (
	SELECT
		id, sku, date,
		cost1, cost2, cost3, num1, num2, num3,
		@total_cost as start_total_cost,
		@total_num as start_total_num,
		@total_cost := @@total_cost - cost1 - cost2 + cost3 as end_total_cost,
		@total_num := @total_num - num1 - num2 + num3 as end_total_num
	FROM 表,
		( SELECT @total_cost:=0, @total_num:=0 ) _x
	ORDER BY date
)b
SET 表.start_total_cost = b.start_total_cost,
	表.start_total_num = b.start_total_num,
	表.end_total_cost = b.end_total_cost,
	表.end_total_num = b.end_total_num
WHERE 表.id = b.id

[深圳phper]

谢谢 ( SELECT @total_cost:=0, @total_num:=0 ) _x 能帮忙解释下这句的意义吗？

引用 10 楼 zjcxc 的回复:

UPDATE 表, (
	SELECT
		id, sku, date,
		cost1, cost2, cost3, num1, num2, num3,
		@total_cost as start_total_cost,
		@total_num as start_total_num,
		@total_cost := @@total_cost - cost1 - cost2 + cost3 as end_total_cost,
		@total_num := @total_num - num1 - num2 + num3 as end_total_num
	FROM 表,
		( SELECT @total_cost:=0, @total_num:=0 ) _x
	ORDER BY date
)b
SET 表.start_total_cost = b.start_total_cost,
	表.start_total_num = b.start_total_num,
	表.end_total_cost = b.end_total_cost,
	表.end_total_num = b.end_total_num
WHERE 表.id = b.id

[深圳phper]

验证了下数据好像是有问题的，我要的结果是 小于当前日期 的累计值，你这边计算的是 单条记录的和。 不知道有没有解决方案 但是还是要谢谢版主

引用 10 楼 zjcxc 的回复:

UPDATE 表, (
	SELECT
		id, sku, date,
		cost1, cost2, cost3, num1, num2, num3,
		@total_cost as start_total_cost,
		@total_num as start_total_num,
		@total_cost := @@total_cost - cost1 - cost2 + cost3 as end_total_cost,
		@total_num := @total_num - num1 - num2 + num3 as end_total_num
	FROM 表,
		( SELECT @total_cost:=0, @total_num:=0 ) _x
	ORDER BY date
)b
SET 表.start_total_cost = b.start_total_cost,
	表.start_total_num = b.start_total_num,
	表.end_total_cost = b.end_total_cost,
	表.end_total_num = b.end_total_num
WHERE 表.id = b.id

[深圳phper]

知道问题了是我写的有问题，专家的结果是ok的，谢谢