declare @table table([卡号] varchar(13),[姓名] varchar(10),[积分] numeric(13,2)) insert into @table select '789789076372','廖少瑜',0.00 union all select '7897890763725','1',1.00 union all select '789789076373','林碧芳',48.00 union all select '7897890763732','1',1.00 ;with cte as ( select [卡号],sum([积分]) as [积分] from ( select left([卡号],12) as [卡号],[姓名],[积分] from @table ) as t group by [卡号] ) select c.[卡号],t.[姓名],c.[积分] from cte c inner join @table t on c.[卡号]=left(t.[卡号],12) where len(t.[卡号])>12 /*卡号 姓名 积分 ------------- ---------- -------------- 789789076372 1 1.00 789789076373 1 49.00(2 行受影响))*/
if object_id('[tb]') is not null drop table [tb] go create table [tb]([卡号] bigint,[姓名] varchar(6),[积分] numeric(4,2)) insert [tb] select 789789076372,'廖少瑜',.00 union all select 7897890763725,'1',.00 union all select 789789076373,'林碧芳',48.00 union all select 7897890763732,'1',1.00 goselect 卡号,姓名, 积分=(select sum(积分) from tb where left(卡号,12)=left(t.卡号,12)) from tb t where len(卡号)=13 /** 卡号 姓名 积分 -------------------- ------ --------------------------------------- 7897890763725 1 0.00 7897890763732 1 49.00(2 行受影响) **/update t set 积分=(select sum(积分) from tb where left(卡号,12)=left(t.卡号,12)) from tb t where len(卡号)=13select * from tb /** 卡号 姓名 积分 -------------------- ------ --------------------------------------- 789789076372 廖少瑜 0.00 7897890763725 1 0.00 789789076373 林碧芳 48.00 7897890763732 1 49.00(4 行受影响) **/
insert into @table
select '789789076372','廖少瑜',0.00 union all
select '7897890763725','1',1.00 union all
select '789789076373','林碧芳',48.00 union all
select '7897890763732','1',1.00
;with cte as
(
select [卡号],sum([积分]) as [积分]
from (
select left([卡号],12) as [卡号],[姓名],[积分] from @table
) as t
group by [卡号]
)
select c.[卡号],t.[姓名],c.[积分] from cte c
inner join @table t on c.[卡号]=left(t.[卡号],12)
where len(t.[卡号])>12
/*卡号 姓名 积分
------------- ---------- --------------
789789076372 1 1.00
789789076373 1 49.00(2 行受影响))*/
go
create table [tb]([卡号] bigint,[姓名] varchar(6),[积分] numeric(4,2))
insert [tb]
select 789789076372,'廖少瑜',.00 union all
select 7897890763725,'1',.00 union all
select 789789076373,'林碧芳',48.00 union all
select 7897890763732,'1',1.00
goselect 卡号,姓名,
积分=(select sum(积分) from tb where left(卡号,12)=left(t.卡号,12))
from tb t
where len(卡号)=13
/**
卡号 姓名 积分
-------------------- ------ ---------------------------------------
7897890763725 1 0.00
7897890763732 1 49.00(2 行受影响)
**/update t
set 积分=(select sum(积分) from tb where left(卡号,12)=left(t.卡号,12))
from tb t
where len(卡号)=13select * from tb
/**
卡号 姓名 积分
-------------------- ------ ---------------------------------------
789789076372 廖少瑜 0.00
7897890763725 1 0.00
789789076373 林碧芳 48.00
7897890763732 1 49.00(4 行受影响)
**/