主页 / dba / 问题 / 129358
Accepted
obautista
Asked: 2016-02-16 17:20:17 +0800 CST

如何使用 CTE 比较值

  • 772

我需要根据以前的记录返回 0 或 1。示例表：

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),    
(101, 1, '20151205', 2),    
(101, 0, '20151206', 3),
(101, 1, '20151208', 2), 
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),    
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

最后一条记录我们唯一关心的是LevelCode（即每个ProductID的最后一条记录）。最后一条记录是否通过/失败无关紧要。然后，我们查看该 ProductID 的所有其他记录（因此最后一条记录之前的所有记录），如果失败，LevelCode 与最后一条记录相同，我们将 IsLastRunSameLevelAsPreviousRun 设置为 1，否则为 0：

 ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

如果 a 没有失败，ProductID则IsLastRunSameLevelAsPreviousRun应该返回 0。

非常感谢任何帮助或提示。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    使用LAG ，您无需任何连接即可实现目标：

    WITH cte AS
  (
    SELECT
      ProductID,
      SampleDate,
      MaxSampleDate = MAX(SampleDate) OVER (PARTITION BY ProductID),
      PrevFailed    = LAG(Failed, 1, 0) OVER (PARTITION BY ProductID, LevelCode
                                              ORDER BY SampleDate)
    FROM
      @x
  )
SELECT
  ProductID,
  IsLastRunSameLevelAsPreviousRun = PrevFailed
FROM
  cte
WHERE
  SampleDate = MaxSampleDate
;

    为每个组中的每一行cte获取相同的先前Failed状态，当没有匹配的行时返回 0。它还计算每个组中的最后一个日期，以便稍后使用它来确定组中的最后一行。LevelCodeProductID

    这是它返回的内容：

    ProductID  SampleDate  MaxSampleDate  PrevFailed
---------  ----------  -------------  ----------
101        2015-12-01  2015-12-08     0
101        2015-12-05  2015-12-08     0
101        2015-12-08  2015-12-08     1
101        2015-12-06  2015-12-08     0
102        2015-12-02  2015-12-05     0
102        2015-12-04  2015-12-05     0
102        2015-12-05  2015-12-05     0
103        2016-01-01  2016-01-03     0
103        2016-01-02  2016-01-03     0
103        2016-01-03  2016-01-03     1
104        2016-01-01  2016-01-03     0
104        2016-01-02  2016-01-03     0
104        2016-01-03  2016-01-03     0

    主 SELECT 本质上只是使用SampleDate = MaxSampleDate过滤器获取每个组的最后一行，只提取ProductID并将PrevFailed后者重命名为IsLastRunSameLevelAsPreviousRun，以便最终输出成为您想要的：

    ProductID  IsLastRunSameLevelAsPreviousRun
---------  -------------------------------
101        1
102        0
103        1
104        0
    • 6

  2. 一种方法是使用ROW_NUMBER两次。

    ProductID首先对由( )分区的表的所有行进行编号，CTE_RowNumbersAll并仅获取每个ProductID( CTE_LastAll) 的最后一行。

    然后对所有失败的行进行编号 ( CTE_RowNumbersFailed) 并获取每个失败的最后一行ProductID( CTE_LastFailed)。

    最后将LEFT JOIN中间结果放在一起并用于CASE比较LevelCode值。

    更新

    根据您的最后评论，我更改了查询。CTE_RowNumbersFailed现在基于CTE_RowNumbersAll并且有一个额外的过滤器rnAll > 1可以从考虑中删除最后一行。

    WITH
CTE_RowNumbersAll
AS
(
    SELECT
        ProductID
        ,Failed
        ,SampleDate
        ,LevelCode
        ,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC) AS rnAll
    FROM @x
)
,CTE_LastAll
AS
(
    SELECT
        ProductID
        ,LevelCode
    FROM CTE_RowNumbersAll
    WHERE rnAll = 1
)
,CTE_RowNumbersFailed
AS
(
    SELECT
        ProductID
        ,Failed
        ,SampleDate
        ,LevelCode
        ,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SampleDate DESC) AS rnFailed
    FROM CTE_RowNumbersAll
    WHERE
        Failed = 1
        AND rnAll > 1
)
,CTE_LastFailed
AS
(
    SELECT
        ProductID
        ,LevelCode
    FROM CTE_RowNumbersFailed
    WHERE rnFailed = 1
)
SELECT
    CTE_LastAll.ProductID
    ,CASE WHEN CTE_LastAll.LevelCode = CTE_LastFailed.LevelCode 
        THEN 1 ELSE 0 END AS IsLastRunSameLevelAsPreviousRun
FROM
    CTE_LastAll
    LEFT JOIN CTE_LastFailed ON CTE_LastFailed.ProductID = CTE_LastAll.ProductID
ORDER BY CTE_LastAll.ProductID;

    结果

    +-----------+---------------------------------+
| ProductID | IsLastRunSameLevelAsPreviousRun |
+-----------+---------------------------------+
|       101 |                               1 |
|       102 |                               0 |
|       103 |                               1 |
|       104 |                               0 |
+-----------+---------------------------------+
    • 5

  3. 希望这可以帮助

    DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);

INSERT @x 
select * 
from
(VALUES (101, 0, '20151201', 1),
(101, 1, '20151205', 2),
(101, 0, '20151206', 3), (101, 1, '20151208', 2), (102, 1, '20151202', 1), (102, 0, '20151204', 2),
(102, 0, '20151205', 3), (103, 0, '20160101', 1), (103, 1, '20160102', 2), (103, 0, '20160103', 2), (104, 0, '20160101', 1), (104, 0, '20160102', 2), (104, 0, '20160103', 3)
) V(ProductID, Failed, SampleDate, LevelCode);


with T as (

select ProductID, 
RANK() OVER (PARTITION BY ProductID ORDER BY SampleDate desc) AS [Rank],
levelcode
from @x
where Failed = 1
)

select  FT.ProductID, FT.LevelCode,
    case
        when FT.LevelCode = ST.LevelCode then 1
        else 0
    END as IsLastRunSameLevelAsPreviousRun
FROM T AS FT
inner join T as ST on FT.ProductID = ST.ProductID and FT.[Rank]+1 = ST.[Rank]
WHERE FT.[Rank] = 1
    • 1

相关问题