27,579
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID(N'tempdb.dbo.#T') is not null
drop table #T
go
create table #T
(Iden int identity(1,1),
Color_Code varchar(10),
Color_Name varchar(20),
Color_Name_CN nvarchar(10),
Lines int)
insert into #T
select 'BL191407','BLUE','兰',780 union all
select 'BL191408','DK BLUE','紫兰',2352 union all
select 'NYE0012','BLUE PRINT','浅上',1584 union all
select 'RW001','WHITE','白',3034 union all
select 'RW011','RED','红',3034
;with cte
as
(select A.*,
cast(CAST(A.Iden as varchar)+','+CAST(B.Iden as varchar) as varchar) as iden_list ,
cast(CAST(A.Color_Name as varchar)+','+CAST(B.Color_Name as varchar) as varchar) as Color_Name_list,
A.Lines+B.Lines as total_line,
B.Iden as next_iden
from #T A
join #T B ON A.Iden<B.Iden
union all
select B.*,
cast(cast(A.iden_list as varchar)+','+CAST(B.Iden as varchar) as varchar),
cast(CAST(A.Color_Name_list as varchar)+','+CAST(B.Color_Name as varchar) as varchar),
B.Lines+A.total_line,
B.Iden
from cte A
join #T B on A.next_iden<B.Iden
)
SELECT * FROM cte
if OBJECT_ID(N'tempdb.dbo.#T') is not null
drop table #T
go
create table #T
(Iden int identity(1,1),
Color_Code varchar(10),
Color_Name varchar(20),
Color_Name_CN nvarchar(10),
Lines int)
insert into #T
select 'BL191407','BLUE','兰',780 union all
select 'BL191408','DK BLUE','紫兰',2352 union all
select 'NYE0012','BLUE PRINT','浅上',1584 union all
select 'RW001','WHITE','白',3034 union all
select 'RW011','RED','红',3034
with cte
as
(select A.*,
cast(CAST(A.Iden as varchar)+','+CAST(B.Iden as varchar) as varchar) as iden_list ,
cast(CAST(A.Color_Name as varchar)+','+CAST(B.Color_Name as varchar) as varchar) as Color_Name_list,
A.Lines+B.Lines as total_line,
B.Iden as next_iden
from #T A
join #T B ON A.Iden<B.Iden
union all
select B.*,
cast(cast(A.iden_list as varchar)+','+CAST(B.Iden as varchar) as varchar),
cast(CAST(A.Color_Name_list as varchar)+','+CAST(B.Color_Name as varchar) as varchar),
B.Lines+A.total_line,
B.Iden
from cte A
join #T B on A.next_iden<B.Iden)