sql - 如何创建存储过程以组合多个条件结果

Question

表结构如下:

TABLE TestTable 
(
       [Id] [uniqueidentifier] NOT NULL, 
       [Quality] [tinyint] NULL,
       [UtcTimeStamp] [datetime2](7) NOT NULL, 
       [Value] [varbinary](max) NULL
)

我要实现的是:对于UtcTimeStamp的指定时间范围(from - to)，首先我会查询所有数据位于时间范围内。然后，按照这个规则继续查询to时间段之后的数据:

• R0:Quality 有三个值:1、2、3
• R1:如果to后的第一个数据Quality为3，则将本次查询结果与上述范围结果合并即可。
• R2:如果to之后的第一个数据Quality是2，则继续查询下一行(在这个UtcTimeStamp之后)哪个Quality为3。最后，将这两个查询结果与上述范围结果合并。
• R3:如果to之后的第一个数据Quality为1，则继续查询下一行(在此UtcTimeStamp之后)Quality为2和3。最后，将这三个查询结果与上述范围结果合并。

以上所有工作都包含在一个存储过程中，并且性能尽可能好。

我对存储过程的语法不是很熟悉，尝试过存储一些中间变量，用IF ELSE 语法把所有的结果组合起来，就是失败了句子有效。

---

已更新

演示内容如下:

对于 R1:

数据库是这样的

Id(fake) UtcTimeStamp(fake) Quality Value
1s-..    1                  1       0x...
1s-..    2                  2       0x...
1s-..    3                  2       0x...
1s-..    4                  3       0x...
1s-..    5                  3       0x...
1s-..    6                  2       0x...

如果我希望查询的时间范围是1-4，那么结果应该是:

Id(fake) UtcTimeStamp(fake) Quality Value
1s-..    1                  1       0x...
1s-..    2                  2       0x...
1s-..    3                  2       0x...
1s-..    4                  3       0x...
1s-..    5                  3       0x...

对于 R2:

数据库是这样的

Id(fake) UtcTimeStamp(fake) Quality Value
1s-..    1                  1       0x...
1s-..    2                  2       0x...
1s-..    3                  2       0x...
1s-..    4                  3       0x...
1s-..    5                  2       0x...
1s-..    6                  2       0x...
1s-..    7                  1       0x...
1s-..    8                  3       0x...

如果我希望查询的时间范围是1-4，那么结果应该是:

Id(fake) UtcTimeStamp(fake) Quality Value
1s-..    1                  1       0x...
1s-..    2                  2       0x...
1s-..    3                  2       0x...
1s-..    4                  3       0x...
1s-..    5                  2       0x...
1s-..    8                  3       0x...

对于 R3:

数据库是这样的

Id(fake) UtcTimeStamp(fake) Quality Value
1s-..    1                  1       0x...
1s-..    2                  2       0x...
1s-..    3                  2       0x...
1s-..    4                  3       0x...
1s-..    5                  1       0x...
1s-..    6                  2       0x...
1s-..    7                  1       0x...
1s-..    8                  3       0x...

如果我希望查询的时间范围是1-4，那么结果应该是:

Id(fake) UtcTimeStamp(fake) Quality Value
1s-..    1                  1       0x...
1s-..    2                  2       0x...
1s-..    3                  2       0x...
1s-..    4                  3       0x...
1s-..    5                  1       0x...
1s-..    6                  2       0x...
1s-..    8                  3       0x...

程序应该是这样的:

CREATE PROCEDURE [ProcedureName] @Id as uniqueidentifier, @StartTime as datetime2, @EndTime as datetime2 AS ...

Answer 1

您可以尝试以下操作。我只是尝试先生成一些演示数据:

-- Create demo data
CREATE TABLE dbo.temp
(
       [Id] [uniqueidentifier] NOT NULL, 
       [Quality] [tinyint] NULL,
       [UtcTimeStamp] [datetime2](7) NOT NULL, 
       [Value] [varbinary](max) NULL
)

INSERT INTO dbo.temp(id, quality,UtcTimeStamp, value)
SELECT NEWID() as id, NTILE(3) OVER(ORDER BY object_id) as quality, 
    DATEADD(day,-NTILE(3) OVER(ORDER BY object_id),GETUTCDATE()) as UtcTimeStamp, 
    HASHBYTES(N'SHA1',CONVERT(nvarchar(36),NEWID())) as value
FROM sys.all_objects

您可以尝试此过程来获取所有内容:

-- Doing the stuff inside the proc
CREATE PROCEDURE dbo.yourProcedure 
    @from datetime2, @to datetime2
AS BEGIN

    ;WITH cte AS(
        -- Prepare data base for the second part of the query
        SELECT t.id, t.Quality, t.UtcTimeStamp, t.Value, ROW_NUMBER() OVER(order by t.UtcTimeStamp) as rn
        FROM dbo.temp as t
        WHERE t.UtcTimeStamp > @to
    )
    -- Get all data based inside the range of @from and @to
    SELECT t.id, t.Quality, t.UtcTimeStamp, t.Value 
    FROM dbo.temp as t
    WHERE t.UtcTimeStamp BETWEEN @from AND @to
    UNION ALL -- You can use union all, as you the following records won't already be in the above result set
    -- Add all rows which are behind the @to date (prepared in the cte)
    SELECT t.id, t.Quality, t.UtcTimeStamp, t.Value 
    FROM cte as t
    WHERE (t.Quality = 3 AND t.rn = 1) -- The first row, if it's quality 3
        -- All rows until quality 3, if the first row is quality 2
        OR ((t.quality = 2 and t.rn = 1) AND (t.rn <= (SELECT TOP 1 rn FROM cte WHERE quality = 3 ORDER By rn)))
        -- All rows until quality 2 or 3, if the first row is quality 1
        OR ((t.quality = 1 and t.rn = 1) AND (t.rn <= (SELECT TOP 1 rn FROM cte WHERE quality IN(2,3) ORDER By rn)))

END
GO

之后调用结果:

DECLARE @from datetime2 = DATEADD(day,-3,GETUTCDATE()), @to datetime2 = DATEADD(day,-2,GETUTCDATE())
SELECT @from, @to, * FROM dbo.temp

EXEC dbo.yourProcedure @from = @from, @to = @to
GO

不要忘记事后清理。

-- Cleanup
DROP TABLE dbo.temp