如何去除3个关键字段的重复记录

[zou_he]

Table
记录如下：

FA FB FC FD FE
01 PASS Q1 OK EA-001
02 NG U1 NO EB-001
03 PASS R1 OK EC-001
04 NG U1 NO ED-001
05 PASS C1 OK EE-001

请问？
该用什么语句查询将 FB,FC,FC字段重复的记录显示唯一？即这份表第2，第4条记录，只显示第2条记录？并且FA,FB,FC,FD,FE5个字段全部显示出来？
FA FB FC FD FE
01 PASS Q1 OK EA-001
02 NG U1 NO EB-001
03 PASS R1 OK EC-001
05 PASS C1 OK EE-001

能实现吗？请指教！

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([FA] nvarchar(22),[FB] nvarchar(24),[FC] nvarchar(22),[FD] nvarchar(22),[FE] nvarchar(26))
Insert #T
select N'01',N'PASS',N'Q1',N'OK',N'EA-001' union all
select N'02',N'NG',N'U1',N'NO',N'EB-001' union all
select N'03',N'PASS',N'R1',N'OK',N'EC-001' union all
select N'04',N'NG',N'U1',N'NO',N'ED-001' union all
select N'05',N'PASS',N'C1',N'OK',N'EE-001'
Go
--测试数据结束
;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY FB,FC,FD ORDER BY FA) rn from #T
)
DELETE FROM cte WHERE rn>1

SELECT * FROM #T

[吉普赛的歌]

--借#1版主测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([FA] nvarchar(22),[FB] nvarchar(24),[FC] nvarchar(22),[FD] nvarchar(22),[FE] nvarchar(26))
Insert #T
select N'01',N'PASS',N'Q1',N'OK',N'EA-001' union all
select N'02',N'NG',N'U1',N'NO',N'EB-001' union all
select N'03',N'PASS',N'R1',N'OK',N'EC-001' union all
select N'04',N'NG',N'U1',N'NO',N'ED-001' union all
select N'05',N'PASS',N'C1',N'OK',N'EE-001'
GO
SELECT a.* FROM #t a INNER JOIN (
	SELECT MIN(FA) AS FA FROM #t GROUP BY FB,FC,FD
) AS b ON a.FA=b.FA
/*
FA	FB	    FC	FD	FE
01	PASS	Q1	OK	EA-001
02	NG	    U1	NO	EB-001
03	PASS	R1	OK	EC-001
05	PASS	C1	OK	EE-001
*/

[二月十六]

额，不用删除是吧，#1代码稍微改一下，不要删除，直接读取

;WITH cte AS (
Select *,ROW_NUMBER()OVER(PARTITION BY FB,FC,FD ORDER BY FA) rn from #T
)
SELECT * FROM cte WHERE rn=1

[zou_he]

#1,#2二位版 主好，谢谢回复。直接运行二位的全部代码，能查询到结果。 但问题是我将下半部分代码运行查询，二位的方法都报错。我要查询的是一个视图。

[吉普赛的歌]

SELECT a.alarmid,
       a.DefectType,
       a.fzpbl,
       a.AlarmType,
       a.fcpbl,
       a.ComponentID,
       a.Name,
       a.BlockID,
       a.CodeText,
       a.Side,
       a.InspectionID,
       a.InspectionDateTime,
       a.fdatetime,
       a.LogonID,
       a.Operator,
       a.Station
FROM   v_bljl  AS a
       INNER JOIN (
                SELECT MIN( CAST(alarmid AS VARCHAR(50)) ) AS alarmid
                FROM   v_bljl
                GROUP BY
                       DefectType,
                       NAME,
                       BlockID
            )  AS b
            ON  a.alarmid = b.alarmid

这样是否可以？

[zou_he]

2#版主你好 错误提示的图片传不上来 错误原因是因为MIN函数的关键字段为uniqueidentifier 类型 ,不支持MIN函数

[zou_he]

SELECT a.alarmid, a.DefectType, a.fzpbl, a.AlarmType, a.fcpbl, a.ComponentID, a.Name, a.BlockID, a.CodeText, a.Side, a.InspectionID, a.InspectionDateTime, a.fdatetime, a.LogonID, a.Operator, a.Station FROM v_bljl AS a INNER JOIN (SELECT MIN(alarmid) AS alarmid FROM v_bljl GROUP BY DefectType, Name, BlockID) AS b ON a.alarmid = b.alarmid

[zou_he]

1#版主你好 执行您的语句,提示出错,SQL文本无法出现在网格窗格和关系图窗格中. 但好像执行的结果是对的. WITH cte AS ( Select *,ROW_NUMBER()OVER(PARTITION BY DefectType, Name, BlockID ORDER BY alarmid) rn from v_bljl ) SELECT * FROM cte WHERE rn=1

[zou_he]

#7 查询结果正确,无错误提示,谢谢.