select ResTypeID,ResID,Rkey=SUM(RKey)
from @Table
group by ResTypeID,ResID
order by ResTypeID,ResID/*-- 结果 --*/
ResTypeID ResID Rkey
----------- ----------- -----------
1 2 11
2 1 3
2 2 4(所影响的行数为 3 行)
from @Table
group by ResTypeID,ResID
order by ResTypeID,ResID/*-- 结果 --*/
ResTypeID ResID Rkey
----------- ----------- -----------
1 2 11
2 1 3
2 2 4(所影响的行数为 3 行)
SET @intRKey = 0
SELECT @intRKey = @intRKey | RKey FROM @table WHERE ResTypeID=1 AND ResID=2SELECT @intRKey但是还没想出办法象用类似Sum聚合函数的方式,求出一个结果集(因为这样系统处理的效率会提升很多)
create TABLE table1
(ResTypeID int, ResID int, RKey int)insert into table1
SELECT 1, 2, 1 UNION ALL
SELECT 1, 2, 6 UNION ALL
SELECT 1, 2, 4 UNION ALL
SELECT 2, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 2, 2, 1 UNION ALL
SELECT 2, 2, 3
--语句
select identity(int,1,1) as id,* into #tab from table1declare @restypeid int
declare @resid int
declare @rkey int
declare @id int
declare @cou intselect restypeid,resid,rkey into #tab1 from #tab where 1 = 2
select top 1 @id = id,@restypeid=restypeid,@resid = resid,@rkey = rkey from #tab order by id
while (@restypeid is not null)
begin
select @cou = count(1) from #tab1 where restypeid = @restypeid and resid = @resid
if (@cou > 0 )
begin
update #tab1 set rkey = rkey | @rkey where restypeid = @restypeid and resid = @resid
end
else
begin
insert into #tab1 select @restypeid,@resid,@rkey
end
delete from #tab where id = @id
set @restypeid = null
select top 1 @id = id,@restypeid=restypeid,@resid = resid,@rkey = rkey from #tab order by id
endselect * from #tab1 order by restypeid,resid--结果
1 2 7
2 1 3
2 2 3
--删除环境
drop table #tab
drop table #tab1drop table table1