update的问题

lsqzx000 2008-03-31 09:19:13
t1结构
jh xjxh tgmc tggc xs
BK 0 a 5 5.00
BK 1 a 5 5.00
BK 2 a 5 5.00
BK 3 a 5 5.00
BK 5 a 5 5.00

UPDATE t1 SET XS=
(SELECT XS FROM
(select JH,XJXH,TGMC,
(
select SUM(isnull(TGGC,0)) from t1 b where B.jh=t1.jh and t1.xjxh>=B.xjxh AND B.XJXH>0 AND t1.TGMC=B.TGMC
)
+
(SELECT XS FROM t1 C WHERE t1.JH=C.JH AND C.XJXH=0 AND C.TGMC=t1.TGMC
) AS XS from t1
) AS T_XS
WHERE t1.JH=T_XS.JH AND t1.XJXH=T_XS.XJXH AND t1.TGMC=T_XS.TGMC
)
WHERE XJXH>0
运行结果如下
jh xjxh tgmc xs
BK 0 a 5.00
BK 1 a 10.00
BK 2 a 15.00
BK 3 a 20.00
BK 5 a 25.00

我想问下 这里update是如何更新的?请详细说下过程
...全文
82 点赞 收藏 11
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ojuju10 2008-03-31
每一次只更新一行
回复
kaikai_kk 2008-03-31
LZ语句,应该是统计tggc列和值,更新到xs中,若tggc不全相等,则老乌龟答案就不对了


declare @tb table(jh varchar(10), xjxh int, tgmc varchar(10), tggc int , xs decimal(18,2))
insert into @tb values('BK', 0, 'a', 5 , 5.00 )
insert into @tb values('BK', 1, 'a', 6 , 5.00 )
insert into @tb values('BK', 2, 'a', 8 , 5.00 )
insert into @tb values('BK', 3, 'a', 10 , 5.00 )
insert into @tb values('BK', 5, 'a', 12 , 5.00 )


--更新
update @tb set xs=(select sum(tggc) from @tb where jh=t.jh and xjxh<=t.xjxh) from @tb t

--查询
select * From @tb

/*

BK 0 a 5 5.00
Bk 1 a 6 11.00
BK 2 a 8 19.00
BK 3 a 10 29.00
Bk 5 a 12 41.00

*/
回复
utpcb 2008-03-31
不用这么麻烦,用个累计即可.
回复
lsqzx000 2008-03-31
那就是 计算一次sum,更新一次啦
回复
dawugui 2008-03-31
根据当前行,找符合记录的SUM(xs)
回复
lsqzx000 2008-03-31
select sum(xs) from tb where jh = t.jh and xjxh <= t.xjxh
这句产生的数据是什么样的?
是计算一次sum,更新一次?
还是全部计算出来,再更新?
回复
dawugui 2008-03-31
如果你还需要tgmc,tggc这两个字段的条件,自己加上即可.
回复
dawugui 2008-03-31
create table tb(jh varchar(10), xjxh int, tgmc varchar(10), tggc int , xs decimal(18,2))
insert into tb values('BK', 0, 'a', 5 , 5.00 )
insert into tb values('BK', 1, 'a', 5 , 5.00 )
insert into tb values('BK', 2, 'a', 5 , 5.00 )
insert into tb values('BK', 3, 'a', 5 , 5.00 )
insert into tb values('BK', 5, 'a', 5 , 5.00 )
go
--查询
select jh,xjxh,tgmc,xs = (select sum(xs) from tb where jh = t.jh and xjxh <= t.xjxh) from tb t
/*
jh xjxh tgmc xs
---------- ----------- ---------- ---------------------------------------
BK 0 a 5.00
BK 1 a 10.00
BK 2 a 15.00
BK 3 a 20.00
BK 5 a 25.00

(5 行受影响)
*/


--更改
update tb set xs = (select sum(xs) from tb where jh = t.jh and xjxh <= t.xjxh) from tb t
select * from tb
/*
jh xjxh tgmc tggc xs
---------- ----------- ---------- ----------- ---------------------------------------
BK 0 a 5 5.00
BK 1 a 5 10.00
BK 2 a 5 15.00
BK 3 a 5 20.00
BK 5 a 5 25.00

(5 行受影响)
*/

drop table tb

回复
lsqzx000 2008-03-31
我只是想知道这句update是如何执行的
回复
dawugui 2008-03-31
不用这么麻烦,用个累计即可.
回复
ojuju10 2008-03-31
ding
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-31 09:19
社区公告
暂无公告