如何在ROW_NUMBER()上进行筛选

Question

我正在尝试从数据集中选择不同的名称，但同时返回其他列。我已经让它在某种程度上工作了，但就是无法想出如何将它结合起来。

我怀疑我需要一个 WITH x（或其他什么）但不确定

这里是代码和它返回的数据图像。从这里开始，我只想显示在图片中用红色圆圈标出的WHERE RN=1

Select
    row_number() over (partition by tagname order by adddate) as RN,
    tagname,
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1) as SCADA_tag, 
    convert(varchar(12) , adddate , 101) as AddDate,
    left(tagname,CHARINDEX('.',tagname)-1) as 'Table',
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid as 'Group',
    [CC_NOTE_LOG].dbo.SCADA_DB.dataset as 'Dataset', 
    [CC_NOTE_LOG].dbo.SCADA_DB.Description as 'Description'
FROM "Buckeye KB".dbo.v_AlarmsWithTagname
join 
    [CC_NOTE_LOG].dbo.SCADA_DB
on 
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1)=[CC_NOTE_LOG].dbo.SCADA_DB.SCADA_SR_TAG
where
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid<>'test' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'Keep%' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'delete%' and
    Tagname not like '%.ES_%' and
    Tagname not like '%.OPC_%'

- Orin Moyer

---

你也可以使用派生表来实现这个功能，"select * from (your_current_query) X where RN=1"。 - James Z

子查询的一个示例（我认为与派生表相同）：https://dev59.com/up3ha4cB1Zd3GeqPY73k#69680897 - rogerdpack

Answer 1

您只需要使用一个通用表达式(CTE)。使用以下语法：;with CTE AS (query) SELECT whatever FROM CTE，请查看下面的代码块。

;with CTE AS (
Select
    row_number() over (partition by tagname order by adddate) as RN,
    tagname,
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1) as SCADA_tag, 
    convert(varchar(12) , adddate , 101) as AddDate,
    left(tagname,CHARINDEX('.',tagname)-1) as 'Table',
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid as 'Group',
    [CC_NOTE_LOG].dbo.SCADA_DB.dataset as 'Dataset', 
    [CC_NOTE_LOG].dbo.SCADA_DB.Description as 'Description'
FROM "Buckeye KB".dbo.v_AlarmsWithTagname
join 
    [CC_NOTE_LOG].dbo.SCADA_DB
on 
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1)=[CC_NOTE_LOG].dbo.SCADA_DB.SCADA_SR_TAG
where
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid<>'test' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'Keep%' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'delete%' and
    Tagname not like '%.ES_%' and
    Tagname not like '%.OPC_%'
)
SELECT * FROM CTE WHERE [RN] = 1

需要注意的是，CTE后面不一定必须跟着一个SELECT语句。请参阅以下链接以获取CTE指南：https://msdn.microsoft.com/en-us/library/ms175972.aspx

- J.S. Orris

---

运行得非常好！我不知道你是如何如此迅速地回应的，但谢谢！ - Orin Moyer

没问题！很高兴能帮忙。+1 鼓励你的好问题格式。 - J.S. Orris