求SQL语句

[kaikai_kk]

数据如下
ID,F1,F2
1 A XXX
2 B XXX
3 C DDD
4 A XXX
5 B NNN
6 A XXX
7 C GGG
8 B XXX

求结果
ID,F1,F2
1 A XXX
2 B XXX
3 C DDD
5 B NNN
7 C GGG

说明
1)ID是自增的
2)F1,F2相同的任取一笔,如A XXX 可以取ID=1,2,4,6任一笔
3)结果要返回ID(重新生成不相同的ID也行)
4)不可使用临时表,函数

[百年树人]

SELECT *
FROM TB T
WHERE NOT EXISTS(SELECT 1 FROM TB WHERE F1=T.F1 AND F2=T.F2 AND ID<T.ID)

[快溜]

select distinct row_number() over(order by f1) as id,f1,f2 from #table

[DataBox-MDX]

USE City;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO
CREATE TABLE A--创建测试数据表
(
ID int identity(1,1) primary key not null,F1 nvarchar(100),F2 nvarchar(100)
)
INSERT INTO A--插入测试数据
select 'A', 'XXX' union all
select 'B', 'XXX' union all
select 'C', 'DDD' union all
select 'A', 'XXX' union all
select 'B', 'NNN' union all
select 'A', 'XXX' union all
select 'C', 'GGG' union all
select 'B', 'XXX'
go
select ID,F1,F2 
from(select *,ROW_NUMBER()over(partition by F1,F2 order by getdate())RN from A) AA 
where AA.RN=1 order by ID
/*
ID          F1                                                                                                   F2
----------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           A                                                                                                    XXX
2           B                                                                                                    XXX
3           C                                                                                                    DDD
5           B                                                                                                    NNN
7           C                                                                                                    GGG
*/

[叶子]

declare @table table (ID int identity(1,1),F1 varchar(1),F2 varchar(3))
insert into @table(F1,F2)
select 'A','XXX' union all
select 'B','XXX' union all
select 'C','DDD' union all
select 'A','XXX' union all
select 'B','NNN' union all
select 'A','XXX' union all
select 'C','GGG' union all
select 'B','XXX'

select min(ID) as ID,F1,F2 from @table group by F1,F2
order by min(ID)
/*
ID          F1   F2
----------- ---- ----
1           A    XXX
2           B    XXX
3           C    DDD
5           B    NNN
7           C    GGG
*/

[dawugui]

直接用四楼的分组取最小即可,不需要1,2,3楼那么麻烦.

[幸运的意外]

select min(ID) as ID,F1,F2 from @table group by F1,F2
order by 1

这个直接按照F1,F2分组不就OK吗？