我现在有2个表
usertabuid ubalance
64 100
65 100
66 100ordertab
oduid odnumber
64 5
66 3
66 1我写了二条联合更新的 SQL 语句 都是为了实现同一个问题--第一条
update usertab set ubalance = ubalance - odnumber from ordertab otb inner join usertab utb on utb.uid=otb.oduid
--第二条
update usertab set ubalance = ubalance - odnumber from ordertab,usertab where usertab.uid=ordertab.oduid
我希望更新后的结果为
uid ubalance
64 95
65 100
66 96但是我现在 使用上述sql语句中的任何一句得到的结果都是
uid ubalance
64 95
65 100
66 97问问各位大哥,我应该怎么写
usertabuid ubalance
64 100
65 100
66 100ordertab
oduid odnumber
64 5
66 3
66 1我写了二条联合更新的 SQL 语句 都是为了实现同一个问题--第一条
update usertab set ubalance = ubalance - odnumber from ordertab otb inner join usertab utb on utb.uid=otb.oduid
--第二条
update usertab set ubalance = ubalance - odnumber from ordertab,usertab where usertab.uid=ordertab.oduid
我希望更新后的结果为
uid ubalance
64 95
65 100
66 96但是我现在 使用上述sql语句中的任何一句得到的结果都是
uid ubalance
64 95
65 100
66 97问问各位大哥,我应该怎么写
set ubalance=ubalance-(select sum(ubalance) from ordertab where usertab.uid=ordertab.oduid)
set ubalance=ubalance-(select sum(ubalance) from ordertab where usertab.uid=ordertab.oduid)
我明白你的意思
set ubalance=ubalance- B.ubalance
FROM usertab T,
(select UID, sum(ubalance) from ordertab GROUP BY UID)B
WHERE usertab.uid=ordertab.oduid
-- Author :SQL77(只为思齐老)
-- Date :2010-03-11 18:41:44
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#usertab
if object_id('tempdb.dbo.#usertab') is not null drop table #usertab
go
create table #usertab([uid] int,[ubalance] int)
insert #usertab
select 64,100 union all
select 65,100 union all
select 66,100
--> 测试数据:#ordertab
if object_id('tempdb.dbo.#ordertab') is not null drop table #ordertab
go
create table #ordertab([oduid] int,[odnumber] int)
insert #ordertab
select 64,5 union all
select 66,3 union all
select 66,1
--------------开始查询--------------------------
update T
set ubalance=ubalance- B.odnumber
FROM #usertab T,
(select oduid, sum(odnumber) odnumber from #ordertab GROUP BY oduid)B
WHERE T.uid=B.oduidSELECT * FROM #usertab
----------------结果----------------------------
/* (所影响的行数为 3 行)
(所影响的行数为 3 行)
(所影响的行数为 2 行)uid ubalance
----------- -----------
64 95
65 100
66 96(所影响的行数为 3 行)
*/
set ubalance=ubalance-odnumber
from usertab u join (select oduid,SUM(odnumber) odnumber from ordertab) o
on u.uid=o.oduid