if object_id('[TB]') is not null drop table [TB] go create table [TB] (卡号 bigint,姓名 nvarchar(4),积分 numeric(4,2)) insert into [TB] select 789789076372,'老李',1.00 union all select 7897890763725,'老周',5.00 union all select 789789076373,'小芳',48.00 union all select 7897890763732,'小王',1.00select * from [TB] select B.卡号,B.姓名,(select SUM(积分) from TB where left(B.卡号,12) = left(卡号,12)) as 积分 from TB B where LEN(B.卡号) =13/* 7897890763725 老周 6.00 7897890763732 小王 49.00/
declare @t table(卡号 varchar(20),姓名 varchar(4),积分 decimal) insert into @t values('789789076372','老李',1.00) insert into @t values('7897890763725','老周',5.00) insert into @t values('789789076373','小芳',48.00) insert into @t values('7897890763732','小王',1.00) ;with cte as (select LEFT(卡号,12) card,SUM(积分) score from @t group by LEFT(卡号,12)) select 卡号,姓名,cte.score as '积分' from @t,cte where (LEFT(卡号,12)=cte.card and LEN(卡号)=13) 卡号 姓名 积分 7897890763725 老周 6 7897890763732 小王 49
if object_id('[TB]') is not null drop table [TB] go create table [TB] (卡号 bigint,姓名 nvarchar(4),积分 numeric(4,2)) insert into [TB] select 789789076372,'老李',1.00 union all select 7897890763725,'老周',5.00 union all select 789789076373,'小芳',48.00 union all select 7897890763732,'小王',1.00select * from [TB]select a.卡号,a.姓名,b.score as 积分 from TB a, (select LEFT(卡号,12) card ,SUM(积分) score from TB group by LEFT(卡号,12) )b where left(a.卡号,12)=b.card and len(a.卡号)=13采用2楼的建表
or
left('',13)
go
create table [TB] (卡号 bigint,姓名 nvarchar(4),积分 numeric(4,2))
insert into [TB]
select 789789076372,'老李',1.00 union all
select 7897890763725,'老周',5.00 union all
select 789789076373,'小芳',48.00 union all
select 7897890763732,'小王',1.00select * from [TB]
select B.卡号,B.姓名,(select SUM(积分) from TB where left(B.卡号,12) = left(卡号,12)) as 积分
from TB B
where LEN(B.卡号) =13/*
7897890763725 老周 6.00
7897890763732 小王 49.00/
declare @t table(卡号 varchar(20),姓名 varchar(4),积分 decimal)
insert into @t values('789789076372','老李',1.00)
insert into @t values('7897890763725','老周',5.00)
insert into @t values('789789076373','小芳',48.00)
insert into @t values('7897890763732','小王',1.00)
;with cte as (select LEFT(卡号,12) card,SUM(积分) score from @t group by LEFT(卡号,12))
select 卡号,姓名,cte.score as '积分' from @t,cte where
(LEFT(卡号,12)=cte.card and LEN(卡号)=13)
卡号 姓名 积分
7897890763725 老周 6
7897890763732 小王 49
go
create table [TB] (卡号 bigint,姓名 nvarchar(4),积分 numeric(4,2))
insert into [TB]
select 789789076372,'老李',1.00 union all
select 7897890763725,'老周',5.00 union all
select 789789076373,'小芳',48.00 union all
select 7897890763732,'小王',1.00select * from [TB]select a.卡号,a.姓名,b.score as 积分 from TB a,
(select LEFT(卡号,12) card ,SUM(积分) score from TB group by LEFT(卡号,12) )b
where left(a.卡号,12)=b.card and len(a.卡号)=13采用2楼的建表