同表更新多列统计数据的问题

[lphy]

UPDATE [Table1] SET b = (select sum(b) from [Table1] where c=1 and a<33) where a=35;
这样更新一列是没问题
我要同时更新多列,该怎么写?
b=sum(b),c=sum(c),d=sum(d)....

[lphy]

[Quote=引用 5 楼 kakajya 的回复:]
貌似没有。
UPDATE [Table1] SET
b = (select sum(b) from [Table1] where c=1 and a <33),
c = (select sum(c) from [Table1] where c=1 and a <33),
d = (select sum(d) from [Table1] where c=1 and a <33)
where a=35

不能实现你的要求吗？
[/Quote]
可以,自己我自己也会写,想征求更简洁的写法:)

[lphy]

[Quote=引用 6 楼 happyflystone 的回复:]
SQL codeUPDATE a
SET
b = b.b,
c = b.c,
d = b.d
from [Table1] a,
(select 35 as a ,sum(b) b,sum(c) c, sum(d) d from [Table1] where c=1 and a <33) b
where a.a = b.a and a=35
[/Quote]
列名 'a' 不明确。

[lphy]

搞定了
感谢happyflystone的启发,最终SQL:
UPDATE aa
SET
b = bb.b,
c = bb.c,
d = bb.d
from [Table1] aa,
(select sum(b) as a ,sum(b) b,sum(c) c,sum(d) d from [Table1] where c=1 and a <33) bb
where aa.a=35

[lphy]

谢谢各位的参与~!

[wangxuelid]

[Quote=引用 1 楼 Limpire 的回复:]
UPDATE [Table1] SET
b = (select sum(b) from [Table1] where c=1 and a <33),
c = (select sum(c) from [Table1] where c=1 and a <33),
...
n = (select sum(n) from [Table1] where c=1 and a <33)
where a=35;
[/Quote]

[Limpire]

UPDATE [Table1] SET
b = (select sum(b) from [Table1] where c=1 and a <33),
c = (select sum(c) from [Table1] where c=1 and a <33),
...
n = (select sum(n) from [Table1] where c=1 and a <33)
where a=35;

[dawugui]

[Quote=引用 1 楼 Limpire 的回复:]
UPDATE [Table1] SET
b = (select sum(b) from [Table1] where c=1 and a <33),
c = (select sum(c) from [Table1] where c=1 and a <33),
...
n = (select sum(n) from [Table1] where c=1 and a <33)
where a=35
[/Quote]
同意.

最后的;可以不要.

[zccmy22]

UPDATE [Table1] SET
b = (select sum(b) from [Table1] where c=1 and a <33),
c = (select sum(c) from [Table1] where c=1 and a <33),
d = (select sum(d) from [Table1] where c=1 and a <33)
where a=35

至于子查询，只要记得结果是结果，不是集合就可以用的。无论多少个都可以的。

[lphy]

晕,有没有更简洁的语句了?
谢谢大家支持!

[kakajya]

貌似没有。
UPDATE [Table1] SET
b = (select sum(b) from [Table1] where c=1 and a <33),
c = (select sum(c) from [Table1] where c=1 and a <33),
d = (select sum(d) from [Table1] where c=1 and a <33)
where a=35

不能实现你的要求吗？

[-狙击手-]

UPDATE a
SET   
b = b.b,
c = b.c,
d = b.d 
from [Table1] a, 
(select 35 as a ,sum(b) b,sum(c) c, sum(d) d from [Table1] where c=1 and a <33) b
where a.a = b.a and a=35