具有多个值的列的TSQL组

I have a table in SQLServer 2008r2 as below.

我在SQLServer 2008r2中有一个表，如下所示。

I want to select all the records where the [Fg] column = 1 that consecutively by [Id] order lead into value 2 for each [T_Id] and [N_Id] combination.

我想选择[Fg]列= 1连续按[Id]顺序导致每个[T_Id]和[N_Id]组合的值为2的所有记录。

There can be instances where record prior to [Fg] = 2 doesn't = 1

可能存在[Fg] = 2之前的记录不= 1的情况

There can be any number of records where the value of [Fg] = 1 but only one record where [Fg] = 2 for each [T_Id] and [N_Id] combination.

可以存在任何数量的记录，其中[Fg]的值= 1，但是对于每个[T_Id]和[N_Id]组合，只有一个记录，其中[Fg] = 2。

So for the example below, I want to select records with [Id]s (4,5) and (7,8,9 )and (19,20).

因此，对于下面的示例，我想选择[Id] s（4,5）和（7,8,9）和（19,20）的记录。

Any records for [T_Id] 3 and 4 are excluded.

[T_Id] 3和4的任何记录都被排除在外。

Expected output

预期产出

Example data set

示例数据集

DECLARE @Data TABLE ( Id INT IDENTITY (1,1), T_Id INT, N_Id INT, Fg TINYINT )

INSERT INTO @Data
(T_Id, N_Id, Fg)
VALUES
(1, 2, 0), (1, 2, 1), (1, 2, 0), (1, 2, 1), (1, 2, 2), (2, 3, 0), (2, 3, 1), 
(2, 3, 1), (2, 3, 2), (3, 4, 0), (3, 4, 0), (3, 4, 0), (3, 4, 2), (4, 5, 0), 
(4, 5, 1), (4, 5, 0), (4, 5, 2), (5, 7, 0), (5, 7, 1), (5, 7, 2)

2 个解决方案

#1

It can be done easily using recursive CTE:

使用递归CTE可以轻松完成：

WITH DataSource AS
(
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN @Data DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] + 1
        AND DS1.[Fg] = 2
        AND DS2.[Fg] = 1
    UNION ALL
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN DataSource DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] - 1
        AND DS1.[Fg] = 1
)
SELECT *
FROM DataSource
ORDER BY Id

The idea is simple. The first part of the query gets all valid records with fg = 2 - valid means there is record before this one with fg = 1 from the same group.

这个想法很简单。查询的第一部分获取fg = 2的所有有效记录 - 有效意味着在此之前有记录，同一组中fg = 1。

Then in the recursive part we are getting all records smaller then initial ones, that has fg = 1.

然后在递归部分，我们得到的所有记录都小于初始记录，即fg = 1。

#2

You cant use lag/lead because it started in SQL 2012, you will need to do something like the below.

您无法使用滞后/潜在客户，因为它始于SQL 2012，您需要执行以下操作。

SELECT  fg - (
        SELECT  TOP 1 fg
        FROM    table m2
        WHERE   m2.fg = m1.fg-1 OR (m2.fg = m1.fg AND m2.id < m1.id)
        ORDER BY 
                fg, id
        )
FROM table m1
ORDER BY
      fg, id

#1