我有下表：
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 |
+------+----------------+------------------------+-----+
我想在序列中输出带有"上一个"、"当前"one_answers"下一个"任务的数据。我希望它看起来像这样：
+------+----------------+------------------------+-----------+-----------+-----------+
| 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 |
+------+----------------+------------------------+-----------+-----------+-----------+
为什么我没有得到上一个和下一个序列？
在查询中，应该使用表别名，并在所有列名前加上别名。
您的问题是没有为第二列[Seq]指定别名。是来自t1还是来自[opTest]？SQL 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()，避免使用子查询
我看到两个问题。
首先，在两个子查询中，您将字段[seq]与其本身进行比较。您必须在[seq]提到的子查询中明确表示要比较的查询级别。如果不是显式的，它只是默认为当前级别。
其次，只过滤两个子查询中的[seq]。这意味着它将显示它的上一个或下一个值，而不管它实际上与[任务]有关。
另外，就像前面的答案提到的那样。为什么不只使用LEAD和LAG函数？