根据当前值按顺序返回上一值和下一值

Question

我有下表：

CREATE TABLE [opTest] 
(
    [name] varchar(50),
    [task] varchar(50),
    [desc] varchar(100),
    [seq] varchar(5)
)

INSERT INTO [opTest] VALUES 
('Yale', 'Paint', 'Prime Part', '100'),
('Yale', 'Paint', 'Paint Part', '200'),
('Yale', 'Assembly', 'Front Wheel Assembly', '100'),
('Yale', 'Assembly', 'Rear Wheel Assembly', '200'),
('Yale', 'Assembly', 'Chain Assembly', '300'),
('Yale', 'Assembly', 'HUB Assembly', '400'),
('Yale', 'Assembly', 'Final Assembly', '500'),
('Yale', 'CNC Inspection', 'CNC Machine Inspection', '100')

+------+----------------+------------------------+-----+
| name | task           | desc                   | seq |
+------+----------------+------------------------+-----+
| Yale | Paint          | Prime Part             | 100 |
| Yale | Paint          | Paint Part             | 200 |
| Yale | Assembly       | Front Wheel Assembly   | 100 |
| Yale | Assembly       | Rear Wheel Assembly    | 200 |
| Yale | Assembly       | Chain Assembly         | 300 |
| Yale | Assembly       | HUB Assembly           | 400 |
| Yale | Assembly       | Final Assembly         | 500 |
| Yale | CNC Inspection | CNC Machine Inspection | 100 |
+------+----------------+------------------------+-----+

我想输出的数据与前面的任务，当前和下一个任务的顺序。我想让它看起来像这样

+------+----------------+------------------------+-----------+-----------+-----------+
| Name | Task           | Description            | Prev Task | Curr Task | Next Task |
+------+----------------+------------------------+-----------+-----------+-----------+
| Yale | Assembly       | Front Wheel Assembly   | NULL      | 100       | 200       |
| Yale | Assembly       | Rear Wheel Assembly    | 100       | 200       | 300       |
| Yale | Assembly       | Chain Assembly         | 200       | 300       | 400       |
| Yale | Assembly       | HUB Assembly           | 300       | 400       | 500       |
| Yale | Assembly       | Final Assembly         | 400       | 500       | NULL      |
| Yale | CNC Inspection | CNC Machine Inspection | NULL      | 100       | NULL      |
| Yale | Paint          | Prime Part             | NULL      | 100       | 200       |
| Yale | Paint          | Paint Part             | 100       | 200       | NULL      |
+------+----------------+------------------------+-----------+-----------+-----------+

我正在使用下面的查询，我不知道为什么它不工作。我用一个字段表做了一个简单的测试，结果成功了。唯一的区别是，这个字段在我的测试中是int，但是我要转换为int，所以它应该不是一个问题。

SELECT
    [Name] AS [Name],
    [Task] AS [Task],
    [Desc] AS [Description],
    (SELECT TOP(1) t1.[Seq] 
     FROM [opTest] t1 
     WHERE CAST(t1.[Seq] AS int) > CAST([Seq] AS int) 
     ORDER BY t1.[Seq] ASC) AS [Prev Task],
    [Seq] AS [Curr Task],
    (SELECT TOP(1) t1.[Seq] 
     FROM [opTest] t1 
     WHERE CAST(t1.[Seq] AS int) < CAST([Seq] AS int) 
     ORDER BY t1.[Seq] DESC) AS [Next Task]
FROM 
    [opTest]
ORDER BY 
    [Name] ASC, [Task] ASC, [Seq] ASC

返回此结果：

+------+----------------+------------------------+-----------+-----------+-----------+
| Name | Task           | Description            | Prev Task | Curr Task | Next Task |
+------+----------------+------------------------+-----------+-----------+-----------+
| Yale | Assembly       | Front Wheel Assembly   | NULL      | 100       | NULL      |
| Yale | Assembly       | Rear Wheel Assembly    | NULL      | 200       | NULL      |
| Yale | Assembly       | Chain Assembly         | NULL      | 300       | NULL      |
| Yale | Assembly       | HUB Assembly           | NULL      | 400       | NULL      |
| Yale | Assembly       | Final Assembly         | NULL      | 500       | NULL      |
| Yale | CNC Inspection | CNC Machine Inspection | NULL      | 100       | NULL      |
| Yale | Paint          | Prime Part             | NULL      | 100       | NULL      |
| Yale | Paint          | Paint Part             | NULL      | 200       | NULL      |
+------+----------------+------------------------+-----------+-----------+-----------+

为什么我没有得到之前和下一个序列？

Answer 1

在查询中，应该使用表别名，并在所有列名前加上别名。

您的问题是没有为第二列Seq指定别名。是t1还是[opTest]的？Server假定它来自t1

  CAST(t1.[Seq] AS int) 
> CAST([Seq] AS int)    -- this is from which table ?

若要修复查询，请向列中添加适当的别名。T.[Seq]

SELECT
    [Name]  AS [Name],
    [Task]  AS [Task],
    [Desc]  AS [Description],
    ( SELECT TOP(1) t1.[Seq] FROM [opTest] t1 
      WHERE CAST(t1.[Seq] AS int) > CAST(T.[Seq] AS int) 
      ORDER BY t1.[Seq] ASC )
            AS [Prev Task],
    [Seq]   AS [Curr Task],
    ( SELECT TOP(1) t1.[Seq] FROM [opTest] t1 
      WHERE CAST(t1.[Seq] AS int) < CAST(T.[Seq] AS int) 
      ORDER BY t1.[Seq] DESC )
            AS [Next Task]
  FROM [opTest] T
ORDER BY [Name] ASC, [Task] ASC, [Seq] ASC

您可以使用LEAD()或LAG()，并避免使用子查询。

Answer 2

我看到了两个问题。

首先，在这两个子查询中，您将字段seq与其本身进行比较。您必须在两个seq中明确提到要比较哪个级别的子查询。当不显式时，它只是默认为当前级别。

其次，您只在两个子查询中筛选seq。这意味着它将显示它的前一个或下一个值，而不管它实际上与任务相关。

同样，就像前面的答案提到的那样。为什么不直接使用铅和滞后函数呢？