--如果可以接受,参考下面的set nocount on if exists(select 1 from sysobjects where id=object_id('Test') and xtype='U') drop table test create table test ( field1 varchar(10), f1 int, f2 int, f3 int )if exists(select 1 from sysobjects where id=object_id('Test1') and xtype='U') drop table test1 create table test1 ( field1 varchar(10), f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int, f8 int, f9 int )insert into test select 'a',1,2,3 union all select 'a',3,4,5 union all select 'b',4,5,6 union all select 'a',7,8,9 union all select 'c',2,9,5 union all select 'c',4,6,8select id=identity(int,1,1),* into # from test select id=identity(int,1,1),a.field1,a.f1,a.f2,a.f3,b.f1 as f4,b.f2 as f5,b.f3 as f6,c.f1 as f7,c.f2 as f8,c.f3 as f9 into #1 from # a left join ( select * from # where id not in (select min(id) from # group by field1) ) b on a.field1=b.field1 left join ( select * from (select * from # where id not in (select min(id) from # group by field1))d where id not in (select min(id) from # where id not in (select min(id) from # group by field1)group by field1) ) c on a.field1=c.field1insert into test1 (field1,f1,f2,f3,f4,f5,f6,f7,f8,f9) select field1,f1,f2,f3,f4,f5,f6,f7,f8,f9 from #1 where id in (select min(id) from #1 group by field1) select * from test select * from test1 drop table test,test1,#,#1 set nocount off /* field1 f1 f2 f3 ---------- ----------- ----------- ----------- a 1 2 3 a 3 4 5 b 4 5 6 a 7 8 9 c 2 9 5 c 4 6 8field1 f1 f2 f3 f4 f5 f6 f7 f8 f9 ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- a 1 2 3 3 4 5 7 8 9 b 4 5 6 NULL NULL NULL NULL NULL NULL c 2 9 5 4 6 8 NULL NULL NULL */
if exists(select 1 from sysobjects where id=object_id('Test') and xtype='U') drop table test
create table test
(
field1 varchar(10),
f1 int,
f2 int,
f3 int
)if exists(select 1 from sysobjects where id=object_id('Test1') and xtype='U') drop table test1
create table test1
(
field1 varchar(10),
f1 int,
f2 int,
f3 int,
f4 int,
f5 int,
f6 int,
f7 int,
f8 int,
f9 int
)insert into test
select 'a',1,2,3 union all
select 'a',3,4,5 union all
select 'b',4,5,6 union all
select 'a',7,8,9 union all
select 'c',2,9,5 union all
select 'c',4,6,8select id=identity(int,1,1),* into # from test
select id=identity(int,1,1),a.field1,a.f1,a.f2,a.f3,b.f1 as f4,b.f2 as f5,b.f3 as f6,c.f1 as f7,c.f2 as f8,c.f3 as f9
into #1
from
# a
left join
(
select * from # where id not in (select min(id) from # group by field1)
) b
on a.field1=b.field1
left join
(
select * from (select * from # where id not in (select min(id) from # group by field1))d
where id not in (select min(id) from # where id not in (select min(id) from # group by field1)group by field1)
) c
on a.field1=c.field1insert into test1
(field1,f1,f2,f3,f4,f5,f6,f7,f8,f9)
select
field1,f1,f2,f3,f4,f5,f6,f7,f8,f9
from #1 where id in (select min(id) from #1 group by field1)
select * from test
select * from test1
drop table test,test1,#,#1
set nocount off
/*
field1 f1 f2 f3
---------- ----------- ----------- -----------
a 1 2 3
a 3 4 5
b 4 5 6
a 7 8 9
c 2 9 5
c 4 6 8field1 f1 f2 f3 f4 f5 f6 f7 f8 f9
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
a 1 2 3 3 4 5 7 8 9
b 4 5 6 NULL NULL NULL NULL NULL NULL
c 2 9 5 4 6 8 NULL NULL NULL
*/