create table #temp
(
i int,
s varchar(10),
a varchar(10)
)declare @i int,
@s varchar(10),
@a varchar(10)declare cur_temp cursor for
select i,s,a from t1open cur_temp fetch next from cur_temp into @i,@s,@awhile @@fetch_status = 0
begin
if not exists(select * from #temp where i=@i or s=@s or a=@a)
begin
insert into #temp values (@i,@s,@a)
end
fetch next from cur_temp into @i,@s,@a
end
close cur_temp
deallocate cur_tempselect * from #temp
(
i int,
s varchar(10),
a varchar(10)
)
insert into #t1 values (1, 'Pear', 's1')
insert into #t1 values (1, 'Apple', 's2')
insert into #t1 values (2, 'Apple', 's3')
insert into #t1 values (2, 'Banana', 's4')
insert into #t1 values (2, 'Egg', 's5')
insert into #t1 values (3, 'Pear', 's6')
insert into #t1 values (3, 'Egg', 's7')
insert into #t1 values (4, 'Banana', 's8')
insert into #t1 values (4, 'Egg', 's9')
insert into #t1 values (5, 'Egg', 's10')
insert into #t1 values (6, 'Banana', 's11')
Select * FROM #t1
SELECT IDENTITY(int,1,1) ID,* INTO #a FROM #t1
Select i,s,a FROM #a where ID IN
(Select Min(ID)
From #a
Group By i)
drop table #a
Drop Table #t1
结果
----
1 Pear s1
1 Apple s2
2 Apple s3
2 Banana s4
2 Egg s5
3 Pear s6
3 Egg s7
4 Banana s8
4 Egg s9
5 Egg s10
6 Banana s11
----
1 Pear s1
2 Apple s3
3 Pear s6
4 Banana s8
5 Egg s10
6 Banana s11
我整理了一下:select identity (int, 1, 1) as ID, * into #a from t1 order by i, (case when s = 'Pear' then 1 when s = 'Apple' then 2 else 3 end)select * from #a where ID in
(select min(ID) from #a group by i)查询结果:ID i s a
----------- ----------- ---------- ----------
1 1 Pear s1
3 2 Apple s3
6 3 Pear s6
8 4 Banana s8
10 5 Egg s10
11 6 Banana s11