如果数据库是SQLSERVER,则:
create table t1(a varchar(10),b varchar(10),c varchar(10),d int)
insert into t1 select 'a','b','c',1 union all
select 'd','e','f',1 union all
select 'g','h','k',2 union all
select 'l','q','p',2
go
;with c1 as(
select ROW_NUMBER() OVER (PARTITION BY d ORDER BY a desc) AS rm,* from t1
),c2 as(
select rm,a,b,c,d from c1 a where rm=1
union all
select b.rm,convert(varchar(10),a.a+b.a),convert(varchar(10),a.b+b.b),convert(varchar(10),a.c+b.c),a.d from c2 a inner join c1 b on a.d=b.d and a.rm=b.rm-1
)
select a,b,c,d from c2 a where not exists(select 1 from c2 where rm>a.rm and d=a.d) order by d
/*
a b c d
---------- ---------- ---------- -----------
da eb fc 1
lg qh pk 2(2 行受影响)*/
go
drop table t1
create table t1(a varchar(10),b varchar(10),c varchar(10),d int)
insert into t1 select 'a','b','c',1 union all
select 'd','e','f',1 union all
select 'g','h','k',2 union all
select 'l','q','p',2
go
;with c1 as(
select ROW_NUMBER() OVER (PARTITION BY d ORDER BY a desc) AS rm,* from t1
),c2 as(
select rm,a,b,c,d from c1 a where rm=1
union all
select b.rm,convert(varchar(10),a.a+b.a),convert(varchar(10),a.b+b.b),convert(varchar(10),a.c+b.c),a.d from c2 a inner join c1 b on a.d=b.d and a.rm=b.rm-1
)
select a,b,c,d from c2 a where not exists(select 1 from c2 where rm>a.rm and d=a.d) order by d
/*
a b c d
---------- ---------- ---------- -----------
da eb fc 1
lg qh pk 2(2 行受影响)*/
go
drop table t1
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货