请教一个实际问题，如何写语句代替游标循环执行，先谢谢各位，

[weixin_38986883]

create table table1(UserType nvarchar(1),UserName nvarchar(50),UserWant numeric(10,0),UserAct numeric(10,0))
insert into table1 values('A','小王',100,0);
insert into table1 values('A','老王',200,0);
insert into table1 values('A','小李',300,0);
insert into table1 values('B','大刘',155,0);

create table table2(UserType nvarchar(1),TotalAct numeric(10,0));
insert into table2 values('A',500);
insert into table2 values('B',100);

这么理解,有A,B,C...几个班级，里面每个人想要多少钱（UserWant)。 A班级总额度500，B班级总额度100
要求按照表里面的顺序一个一个循环去更新最终能拿到多少，排在后面的不够的就拿零头，再后面的就没有
table1最终被更新成这个样子：
A 小王 100 100
A 老王 200 200
A 小李 300 200
B 老王 155 100

以上是我举例，数据比较少，用游标可以实现，但是效率很低，有没其他更新数据的办法？ 谢谢

[zjcxc]

你的示例数据少一个能反应顺序的列，因为数据库中是不存在存储顺序这回事的，所以需要一个列来明确顺序 比如在表1加个自增列

[zjcxc]

--  给 table1 加个自增列来确保顺序
alter table table1 add id int identity;
go
-- 更新
update a
set UserAct = case
		when c.UserWant<=b.TotalAct then a.UserWant
		else b.TotalAct - (c.UserWant-a.UserWant)
	end
from table1 a, table2 b, (
		select id, UserWant=sum(UserWant) over(partition by UserType order by id)
		from table1
	) c
where a.UserType = b.UserType and a.id = c.id
	and c.UserWant - a.UserWant <= b.TotalAct
;
select * from table1

[听雨停了]

create table table1(UserType nvarchar(1),UserName nvarchar(50),UserWant numeric(10,0),UserAct numeric(10,0))
insert into table1 values('A','小王',100,0);
insert into table1 values('A','老王',200,0);
insert into table1 values('A','小李',300,0);
insert into table1 values('A','小明',300,0);
insert into table1 values('B','大刘',155,0);

create table table2(UserType nvarchar(1),TotalAct numeric(10,0));
insert into table2 values('A',500);
insert into table2 values('B',100);

;WITH cte AS (
	SELECT a.*,b.TotalAct,ROW_NUMBER() OVER(PARTITION BY a.UserType ORDER BY (SELECT 1)) AS rn 
	FROM table1 a
	INNER JOIN table2 b ON a.UserType=b.UserType
),cte2 AS (
	SELECT *,CAST((totalact-userwant) AS NUMERIC(10,0)) AS cnt FROM cte WHERE rn=1
	UNION ALL 
	SELECT a.*,cast((b.cnt-a.userwant) AS NUMERIC(10,0) ) FROM cte a
	INNER JOIN cte2 b ON a.usertype=b.usertype AND a.rn=b.rn+1	
),cte3 AS (
	SELECT usertype,username,userwant,CASE WHEN cnt>=0 THEN userwant ELSE userwant+cnt END AS useract  
	FROM cte2
),cte4 AS (
	SELECT usertype,username,userwant,CASE WHEN useract>0   THEN useract ELSE 0 END AS useract 
	FROM cte3
)
UPDATE a SET a.useract=b.useract
FROM table1 a
INNER JOIN cte4 b ON a.UserType=b.usertype AND a.username=b.username	

SELECT * FROM table1
ORDER BY usertype

[weixin_38986883]

你好，非常感谢回复，我用的是Sql Server 2008 R2
执行的时候报错了，麻烦再帮忙确认一下，是不支持吗？

select id, UserWant=sum(UserWant) over(partition by UserType order by id)
from table1

这句话提示order附近有错误

引用 2 楼 zjcxc 的回复:

--  给 table1 加个自增列来确保顺序
alter table table1 add id int identity;
go
-- 更新
update a
set UserAct = case
		when c.UserWant<=b.TotalAct then a.UserWant
		else b.TotalAct - (c.UserWant-a.UserWant)
	end
from table1 a, table2 b, (
		select id, UserWant=sum(UserWant) over(partition by UserType order by id)
		from table1
	) c
where a.UserType = b.UserType and a.id = c.id
	and c.UserWant - a.UserWant <= b.TotalAct
;
select * from table1

[吉普赛的歌]

SQL Server2012+

USE tempdb
GO
IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
create table table1(UserType nvarchar(1),UserName nvarchar(20),UserWant numeric(10,0),UserAct numeric(10,0))
insert into table1 values('A','小王',100,0);
insert into table1 values('A','老王',200,0);
insert into table1 values('A','小李',300,0);
insert into table1 values('B','大刘',155,0);
create table table2(UserType nvarchar(1),TotalAct numeric(10,0));
insert into table2 values('A',500);
insert into table2 values('B',100);

;WITH cte AS(
	SELECT *,sum(userwant) OVER (PARTITION BY userType ORDER BY UserWant ASC) AS total FROM table1 AS t 
),cte2 AS(
SELECT *,(SELECT TOP 1 TotalAct FROM table2 AS b WHERE a.userType=b.UserType) AS ActTotal 
FROM cte a
)
--SELECT * FROM cte2
,cte3 AS (
SELECT UserType,UserName,UserWant,CASE WHEN ActTotal-total>=0 THEN UserWant ELSE UserWant-(Total-ActTotal) END AS UserAct FROM cte2
)

UPDATE table1  
SET UserAct = b.UserAct
FROM cte3 AS b
WHERE table1.UserType=b.UserType AND table1.UserName=b.UserName

SELECT * FROM table1 AS t
/*
UserType UserName             UserWant                                UserAct
-------- -------------------- --------------------------------------- ---------------------------------------
A        小王                   100                                     100
A        老王                   200                                     200
A        小李                   300                                     200
B        大刘                   155                                     100
*/

[吉普赛的歌]

--适用于 SQLServer2005+
USE tempdb
GO
IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
create table table1(UserType nvarchar(1),UserName nvarchar(20),UserWant numeric(10,0),UserAct numeric(10,0))
insert into table1 values('A','小王',100,0);
insert into table1 values('A','老王',200,0);
insert into table1 values('A','小李',300,0);
insert into table1 values('B','大刘',155,0);
create table table2(UserType nvarchar(1),TotalAct numeric(10,0));
insert into table2 values('A',500);
insert into table2 values('B',100);

;WITH base AS(
	SELECT ROW_NUMBER()OVER(PARTITION BY userType ORDER BY UserWant ASC) AS rid,* FROM table1	
)
, cte AS(
	SELECT *,(SELECT SUM(UserWant) FROM base AS b WHERE b.rid<=a.rid AND b.UserType=a.UserType) AS total
	FROM base AS a
)
,cte2 AS(
SELECT *,(SELECT TOP 1 TotalAct FROM table2 AS b WHERE a.userType=b.UserType) AS ActTotal 
FROM cte a
)
,cte3 AS (
SELECT UserType,UserName,UserWant,CASE WHEN ActTotal-total>=0 THEN UserWant ELSE UserWant-(Total-ActTotal) END AS UserAct FROM cte2
)

UPDATE table1  
SET UserAct = b.UserAct
FROM cte3 AS b
WHERE table1.UserType=b.UserType AND table1.UserName=b.UserName

SELECT * FROM table1 AS t
/*
UserType UserName             UserWant                                UserAct
-------- -------------------- --------------------------------------- ---------------------------------------
A        小王                   100                                     100
A        老王                   200                                     200
A        小李                   300                                     200
B        大刘                   155                                     100
*/