时间戳

首页 > 解决方案 > TSQL 基于条件的第二次标记，但也基于其他的结束标记

sql-server - TSQL 基于条件的第二次标记，但也基于其他的结束标记

问题描述

尝试实现一些代码，我在其中创建了两个标记条件结束的列。我将从代码开始：

CREATE TABLE #SampleT (ID INT, SomeVal VARCHAR (10))
INSERT INTO #SampleT VALUES (10, 'X') 
INSERT INTO #SampleT VALUES (20, 'X')
INSERT INTO #SampleT VALUES (30, 'X')
INSERT INTO #SampleT VALUES (40, 'Y')
INSERT INTO #SampleT VALUES (50, 'Y')
INSERT INTO #SampleT VALUES (60, 'Y')
INSERT INTO #SampleT VALUES (70, 'W')
INSERT INTO #SampleT VALUES (80, 'W')
INSERT INTO #SampleT VALUES (90, 'W')
INSERT INTO #SampleT VALUES (100, 'Z')
INSERT INTO #SampleT VALUES (110, 'Z')
INSERT INTO #SampleT VALUES (120, 'Z')

WITH CTE AS 

(
    SELECT ID, 
         SomeVal,
         ROW_NUMBER() OVER (ORDER BY SomeVal) AS RowNumb
    FROM #SampleT AS S
)

SELECT C.*, 
      CASE WHEN C.SomeVal <> C2.SomeVal THEN '**' ELSE '' END AS EndMarking,
      (C.RowNumb / 11) + 1 AS SecondEndMarking
FROM CTE AS C
LEFT JOIN CTE AS C2 ON C.RowNumb = C2.RowNumb - 1
ORDER BY C.RowNumb

DROP TABLE #SampleT

基本上，这段代码工作正常。但我希望第二个结束标记从 Endmarking 列的前一个结束标记开始。在此示例中，我希望列 SecondEndMarking 在第 9 行从 1 更改为 2，因为那是以前的 Enmarking 填充 ** 的时候。

期望的结果：

ID  SomeVal RowNumb EndMarking  SecondEndMarking
70  W   1       1
80  W   2       1
90  W   3   **  1
10  X   4       1
20  X   5       1
30  X   6   **  1
40  Y   7       1
50  Y   8       1
60  Y   9   **  2
100 Z   10      2
110 Z   11      2
120 Z   12      2

这听起来可能有点含糊，但是按照说明的示例代码和所需的输出，我希望有人可以帮助我！

谢谢。

标签： sql-servertsqlsql-server-2008

解决方案

如果我理解正确，你可以试试这个。

由于sql-server 2008不支持LEAD功能，您可以尝试使用子查询来达到 LEAD 功能效果

CREATE TABLE SampleT (ID INT, SomeVal VARCHAR (10))
INSERT INTO SampleT VALUES (10, 'X') 
INSERT INTO SampleT VALUES (20, 'X')
INSERT INTO SampleT VALUES (30, 'X')
INSERT INTO SampleT VALUES (40, 'Y')
INSERT INTO SampleT VALUES (50, 'Y')
INSERT INTO SampleT VALUES (60, 'Y')
INSERT INTO SampleT VALUES (70, 'W')
INSERT INTO SampleT VALUES (80, 'W')
INSERT INTO SampleT VALUES (90, 'W')
INSERT INTO SampleT VALUES (100, 'Z')
INSERT INTO SampleT VALUES (110, 'Z')
INSERT INTO SampleT VALUES (120, 'Z')

查询 1：

;WITH CTE AS 
(
    SELECT ID, 
         SomeVal,
         ROW_NUMBER() OVER (ORDER BY SomeVal) AS RowNumb
    FROM SampleT t1
)
SELECT *,(CASE WHEN MAX(CASE WHEN EndMarking ='**' THEN RowNumb END) OVER (ORDER BY RowNumb DESC) > RowNumb THEN 1 ELSE 2 END) SecondEndMarking
FROM (
  SELECT *,CASE WHEN ( SELECT top 1 SomeVal 
      FROM cte tt
      WHERE tt.RowNumb > t1.RowNumb
      ORDER BY SomeVal )  <> SomeVal  THEN '**'
  ELSE '' END EndMarking  
  FROM CTE t1
) t1
order by RowNumb

结果：

|  ID | SomeVal | RowNumb | EndMarking | SecondEndMarking |
|-----|---------|---------|------------|------------------|
|  70 |       W |       1 |            |                1 |
|  80 |       W |       2 |            |                1 |
|  90 |       W |       3 |         ** |                1 |
|  10 |       X |       4 |            |                1 |
|  20 |       X |       5 |            |                1 |
|  30 |       X |       6 |         ** |                1 |
|  40 |       Y |       7 |            |                1 |
|  50 |       Y |       8 |            |                1 |
|  60 |       Y |       9 |         ** |                2 |
| 100 |       Z |      10 |            |                2 |
| 110 |       Z |      11 |            |                2 |
| 120 |       Z |      12 |            |                2 |

推荐阅读