--测试数据
create table 表(a varchar(10),b varchar(10),c money,d varchar(10),e money)
insert 表 select 'pur_b','m0',20.0000,'X',20.0000
union all select 'pur_b','m0',20.0000,'XX',40.0000
union all select 'pur_b','m0',20.0000,'m1',20.0000
union all select 'pur_b','m0',20.0000,'m2',20.0000
union all select 'pur_b','m0',20.0000,'m3',20.0000
union all select 'pur_b','m0',20.0000,'m4',20.0000
union all select 'pur_b','m2',10.0000,'m2-1',10.0000
union all select 'pur_b','m2',10.0000,'m2-2',10.0000
union all select 'pur_b','m2',10.0000,'m2-3',10.0000
union all select 'pur_b','m2',10.0000,'m2-4',10.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-1',50.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-2',100.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-3',50.0000
go--处理
select id=identity(int,1,1),* into #t from 表select a=case id when (select min(id) from #t where a=a.a)
then a else '' end
,b=case id when (select min(id) from #t where a=a.a and b=a.b)
then b else '' end
,c,d,e
from #t adrop table #t
go--删除测试环境
drop table 表
create table 表(a varchar(10),b varchar(10),c money,d varchar(10),e money)
insert 表 select 'pur_b','m0',20.0000,'X',20.0000
union all select 'pur_b','m0',20.0000,'XX',40.0000
union all select 'pur_b','m0',20.0000,'m1',20.0000
union all select 'pur_b','m0',20.0000,'m2',20.0000
union all select 'pur_b','m0',20.0000,'m3',20.0000
union all select 'pur_b','m0',20.0000,'m4',20.0000
union all select 'pur_b','m2',10.0000,'m2-1',10.0000
union all select 'pur_b','m2',10.0000,'m2-2',10.0000
union all select 'pur_b','m2',10.0000,'m2-3',10.0000
union all select 'pur_b','m2',10.0000,'m2-4',10.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-1',50.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-2',100.0000
union all select 'pur_a','m2-1',50.0000,'m2-1-3',50.0000
go--处理
select id=identity(int,1,1),* into #t from 表select a=case id when (select min(id) from #t where a=a.a)
then a else '' end
,b=case id when (select min(id) from #t where a=a.a and b=a.b)
then b else '' end
,c,d,e
from #t adrop table #t
go--删除测试环境
drop table 表
declare @t1 table(ComId char(2),height char(2))
insert @t1 select 'A1','oo'
insert @t1 select 'A1','hh' declare @t2 table(ComId char(2),width char(2))
insert @t2 select 'A1','T2'
insert @t2 select 'A1','T3'
insert @t2 select 'A1','T4'
--处理
select id=identity(int,1,1),* into #t from
(select a.comid,a.height,b.width from @t1 a,@t2 b where a.comid = b.comid) aaselect ComId=case id when (select min(id) from #t where ComId=a.ComId)
then ComId else '' end
,height=case id when (select min(id) from #t where ComId=a.ComId and height=a.height)
then height else '' end
,width
from #t a--删除测试环境drop table #t
go
/*
ComId height width
----- ------ -----
A1 oo T2
T3
T4
hh T2
T3
T4*/
A1 00 T2
hh T3
T4
go
create table #t1(ComId char(2),height char(2))
insert #t1 select 'A1','oo'
insert #t1 select 'A1','hh'
go
create table #t2(ComId char(2),width char(2))
insert #t2 select 'A1','T2'
insert #t2 select 'A1','T3'
insert #t2 select 'A1','T4'
goselect *,row=1 into #a from #t1 order by ComId
select *,row=1 into #b from #t2 order by ComId
go
declare @ComId char(2),@i int
update #a
set @i=case when ComId=@ComId then @i+1 else 1 end,row=@i,@ComId=ComIdset @i=0
update #b
set @i=case when ComId=@ComId then @i+1 else 1 end,row=@i,@ComId=ComId
go
select
[ComId]=case when a.row=(select min(row) from #b where ComId=b.ComId) then b.ComId else ''end,
[height]=isnull(a.height,''),
b.width
from
#b b
left join
#a a on b.ComId=a.ComId and b.row=a.row
/*
ComId height width
----- ------ -----
A1 oo T2
hh T3
T4(所影响的行数为 3 行)*/
--用变量更新row记录递增列
--关连用left join 左联。。
实际情况楼主要要据情况写,因为记录数有可能a表多也有可能b表多..如果是这样要用full join 连接