sql—仅选择seqnum为特定值的行

yftpprvb 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(242)

我有下面的脚本，如何只包含seqnum=1的行。现在所有的东西都显示出来了，我只想看到[longname]唯一的行。我不能把where子句放在seqnum周围，它不会识别列。

SELECT [LongName]
     , [InstanceId]
     , [Number]   
     , [Value]
     , [Employee]
     , [Time]
     , DENSE_RANK() over (partition by [LongName] order by [Time] desc) as seqnum 
FROM [DataValue]
INNER JOIN [NumberPlate] ON DataValueId = NumberPlate.Id
WHERE [Number] LIKE '%55AA0%' AND [Employee]=1 
ORDER BY [Time] DESC

我的查询的作用

InstanceID  Number  Value       LongName    Employee    Time    seqnum
40            1      5105.993   Parameter C    1    05:10.0   1
20            1      5117.298   Parameter D    1    05:10.0   1
12            1      5126.925   Parameter E    0    05:10.0   1
46            1      5132.83    Parameter B    4    05:10.0   1
453           1      GG2        Parameter A    0    05:10.0   1
345           1      55D        Parameter B    0    05:09.9   2
234           1      5287.562   Parameter D    1    05:09.9   2
865           1      5310.893   Parameter C    2    05:09.9   2
23            1      5105.993   Parameter B    2    05:09.9   2
245           1      GG3        Parameter A    3    03:57.8   2

我想要什么

InstanceID  Number  Value       LongName    Employee    Time    seqnum
40            1      5105.993   Parameter C    1    05:10.0   1
20            1      5117.298   Parameter D    1    05:10.0   1
12            1      5126.925   Parameter E    0    05:10.0   1
46            1      5132.83    Parameter B    4    05:10.0   1
453           1      GG2        Parameter A    0    05:10.0   1

sql sql-server tsql

来源：https://stackoverflow.com/questions/62843844/only-select-rows-where-seqnum-is-a-specific-value

3条答案

按热度按时间

fae0ux8s1#

您也可以使用cte，如下所示：

;WITH CTE AS (
SELECT [LongName]
      ,[InstanceId]
      ,[Number]   
      ,[Value]
      ,[Employee]
      ,[Time]
      ,DENSE_RANK() OVER (PARTITION BY [LongName] ORDER BY [Time] DESC) AS seqnum
FROM 
   [DataValue]
    INNER JOIN [NumberPlate] ON DataValueId = NumberPlate.Id
WHERE
    [Number] LIKE '%55AA0%' AND [Employee]=1 
)
SELECT *
FROM 
    CTE
WHERE 
    seqnum = 1 
ORDER BY 
    [Time] 
DESC

赞(0）回复(0）举报 2021-07-24

8iwquhpp2#

这是一个有趣的方法 SELECT WITH TIES :

SELECT TOP (1) WITH TIES [LongName], [InstanceId], [Number]   , [Value], [Employee], [Time]
FROM [DataValue] INNER JOIN
     [NumberPlate]
     ON DataValueId = NumberPlate.Id
WHERE [Number] LIKE '%55AA0%' AND [Employee]=1 
ORDER BY DENSE_RANK() over (partition by [LongName] order by [Time] desc) as seqnum ;

如果你真的想要最后的结果 time ，但是，您需要一个附加的子查询。

赞(0）回复(0）举报 2021-07-24

v09wglhw3#

仅在子查询中查询：

Select * from 
(SELECT [LongName]
      ,[InstanceId]
      ,[Number]   
      ,[Value]
      ,[Employee]
      ,[Time]
       ,DENSE_RANK() over (partition by [LongName] order by [Time] desc) as seqnum 

  FROM [DataValue]
  INNER JOIN [NumberPlate]
  On DataValueId = NumberPlate.Id

 WHERE [Number] LIKE '%55AA0%' AND [Employee]=1 
  ORDER BY [Time] DESC)
where seqnum = 1;

赞(0）回复(0）举报 2021-07-24

我来回答

sql—仅选择seqnum为特定值的行

3条答案

相关问题

热门标签

最新问答