sql-server - 如何过滤 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_%'

Answer 1

您只需要一个公用表表达式 (CTE)。为此，您可以使用以下语法:;with CTE AS(查询)从 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