sql - T SQL 每天只选择第一条记录

Question

我创建了一个 View ，其中包含 SQL 数据库中的特定表，我可以成功地从一个日期范围内的所有表中选择所有样本。这是 View 设计的一部分:

SELECT 'PLANT FLOW1' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL63
UNION
SELECT 'PLANT FLOW2' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL10
UNION
SELECT 'INFLUENT FLOW' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL1
UNION
SELECT 'EFFLUENT FLOW' AS 'Tag', ts AS 'Timestamp', value AS 'Data'
FROM dbo.UASTP_150000_TL2
UNION

我只需要从每个表中选择每天的第一个读数。我找到了一些示例代码并尝试将其合并，但它不起作用。

错误的 T-SQL 代码:

SELECT TOP (100) percent [Tag]
      ,[Timestamp]
      ,[Data]
  FROM [enteliwebDB].[dbo].[WIMS_View]
  where Timestamp >= DATEADD(day, -30, getdate())
  and Timestamp <= getdate()
  and where Tag in(
    SELECT min(Timestamp)
  )
  order by Tag, Timestamp

当我只对数据范围运行查询时，这是我得到的输出类型，其中包括每个表的所有样本:

Tag Timestamp   Data
9012 TURBIDITY METER TREND  2019-03-10 11:41:14.0680000 5.1552605629
9012 TURBIDITY METER TREND  2019-03-10 11:51:14.0630000 5.14915704727
9012 TURBIDITY METER TREND  2019-03-10 12:01:14.0600000 5.12321662903
9012 TURBIDITY METER TREND  2019-03-10 12:11:14.0560000 5.16212701797
9012 TURBIDITY METER TREND  2019-03-10 12:21:14.0540000 5.12703132629
9012 TURBIDITY METER TREND  2019-03-10 12:31:14.0500000 5.13313484192
9012 TURBIDITY METER TREND  2019-03-10 12:41:14.0470000 5.17814922333
9012 TURBIDITY METER TREND  2019-03-10 12:51:14.0410000 5.14000177383

你能告诉我如何每天从每个表中提取第一个样本吗？

Answer 1

试试这个查询。它使用带有 ROW_NUMBER 的子查询来确定按时间戳排序的每个表的第一条记录:

SELECT [Tag],
       [Timestamp],
       [Data]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY ts DESC PARTITION BY Tag) [rn],
           [Tag],
           [Timestamp],
           [Data]
    FROM MyView
) [a] WHERE [rn] = 1;