代码之家 › 专栏 › 技术社区 › Lajith

如何根据条件获取上一行和下一行

sql-server

Lajith · 技术社区 · 7 年前

我正在尝试获取有关获取所选行的前一行和下一行的语句。

Declare @OderDetail table
(
    Id int primary key,
    OrderId int,
    ItemId int,
    OrderDate DateTime2,
    Lookup varchar(15)
)

INSERT INTO @OderDetail 
VALUES  
(1, 10, 1, '2018-06-11', 'A'), 
(2, 10, 2, '2018-06-11', 'BE'), --this
(3, 2, 1, '2018-06-04', 'DR'),
(4, 2, 2, '2018-06-04', 'D'),  --this
(5, 3, 2, '2018-06-14', 'DD'), --this
(6, 4, 2, '2018-06-14', 'R');


DECLARE 
    @ItemId int = 2,
    @orderid int = 10

所需输出:

程序的输入是order id=10和item id=2,我需要检查item-2是否在任何其他订单中,即根据订单日期,仅匹配记录/订单的前一项和下一项。

4 回复 | 直到 7 年前

level3looper 7 年前

这是你想要的吗?

Declare @OderDetail table
     (Id int primary key,OrderId int,ItemId INT,Lookup varchar(15))

Insert @OderDetail values (1,1, 1,'A')
Insert @OderDetail values (2,1, 2,'BE')
Insert @OderDetail values (3,2, 1,'DR')
Insert @OderDetail values (4,2,2, 'D')
Insert @OderDetail values (5,3,2, 'DD')
Insert @OderDetail values (6,4,2, 'R');

declare @ItemId  int=2 , @orderid int = 2;

查询

With cte As
(
Select ROW_NUMBER() OVER(ORDER BY OrderDate) AS RecN,
* 
From @OderDetail Where ItemId=@ItemId
) 
Select * From cte Where 
RecN Between ((Select Top 1 RecN From cte Where OrderId = @orderid) -1) And
((Select Top 1 RecN From cte Where  OrderId = @orderid) +1) 
Order by id

结果:

RecN    Id  OrderId ItemId  Lookup
1       2   1       2       BE
2       4   2       2       D
3       5   3       2       DD

Zhorov 7 年前

另一种可能的方法是 LAG() 和 LEAD() 函数,从上一行和下一行返回的数据形成相同的结果集。

-- Table
DECLARE @OrderDetail TABLE (
    Id int primary key,
    OrderId int,
    ItemId int,
    OrderDate DateTime2,
    Lookup varchar(15)
)
INSERT INTO @OrderDetail 
VALUES  
   (1, 10, 1, '2018-06-11', 'A'), 
   (2, 10, 2, '2018-06-11', 'BE'), --this
   (3, 2, 1, '2018-06-04', 'DR'),
   (4, 2, 2, '2018-06-04', 'D'),  --this
   (5, 3, 2, '2018-06-14', 'DD'), --this
   (6, 4, 2, '2018-06-14', 'R');

-- Item and order
DECLARE 
    @ItemId int = 2,
    @orderid int = 10

-- Statement    
-- Get previois and next ID for every order, grouped by ItemId, ordered by OrderDate
;WITH cte AS (
   SELECT
      Id,
      LAG(Id, 1) OVER (PARTITION BY ItemId ORDER BY OrderDate) previousId,
      LEAD(Id, 1) OVER (PARTITION BY ItemId ORDER BY OrderDate) nextId,
      ItemId,
      OrderId,
      Lookup
   FROM @OrderDetail   
)
-- Select current, previous and next order
SELECT od.*
FROM cte
CROSS APPLY (SELECT * FROM @OrderDetail WHERE Id = cte.Id) od
WHERE (cte.OrderId = @orderId) AND (cte.ItemId = @ItemId)
UNION ALL
SELECT od.*
FROM cte
CROSS APPLY (SELECT * FROM @OrderDetail WHERE Id = cte.previousId) od
WHERE (cte.OrderId = @orderId) AND (cte.ItemId = @ItemId)
UNION ALL
SELECT od.*
FROM cte
CROSS APPLY (SELECT * FROM @OrderDetail WHERE Id = cte.nextId) od
WHERE (cte.OrderId = @orderId) AND (cte.ItemId = @ItemId)

输出:

Id  OrderId ItemId  OrderDate           Lookup
2   10      2       11/06/2018 00:00:00 BE
4   2       2       04/06/2018 00:00:00 D
5   3       2       14/06/2018 00:00:00 DD

Mark Schultheiss 7 年前

更新到给定的数据集:我知道你要用它做什么。请注意,在某些情况下,给定行之前没有行-因此它只返回2而不是3。这里我更新了CTE版本。取消对另一行的注释以查看3而不是2,因为所选行前面有一行具有该项ID。

添加了一个变量来演示如何更好地实现这一点,如果您更改了该数字(即传递参数),那么允许您在前后获取1,或者在前后获取2,如果行数少,或者在约束内获取尽可能多的行数,则允许您在前后获取1。

所有版本的数据设置:

Declare @OderDetail table
(
    Id int primary key,
    OrderId int,
    ItemId int,
    OrderDate DateTime2,
    Lookup varchar(15)
)

INSERT INTO @OderDetail 
VALUES  
(1, 10, 1, '2018-06-11', 'A'), 
(2, 10, 2, '2018-06-11', 'BE'), --this
(3, 2, 1, '2018-06-04', 'DR'),
(4, 2, 2, '2018-06-04', 'D'),  --this
(5, 3, 2, '2018-06-14', 'DD'), --this
(9, 4, 2, '2018-06-14', 'DD'), 
(6, 4, 2, '2018-06-14', 'R'),
--(10, 10, 2, '2018-06-02', 'BE'), -- un-comment to see one before
(23, 4, 2, '2018-06-14', 'R');

DECLARE 
    @ItemId int = 2,
    @orderid int = 2;

CTE更新版本:

DECLARE @rowsBeforeAndAfter INT = 1;
;WITH cte AS (
    SELECT 
        Id,
        OrderId,
        ItemId,
        OrderDate,
        [Lookup],
        ROW_NUMBER() OVER (ORDER BY OrderDate,Id) AS RowNumber
    FROM @OderDetail
    WHERE 
        ItemId = @itemId -- all matches of this
),
myrow AS (
    SELECT TOP 1
        Id,
        OrderId,
        ItemId,
        OrderDate,
        [Lookup],
        RowNumber
    FROM cte
    WHERE 
        ItemId = @itemId 
        AND OrderId = @orderid
)
SELECT 
    cte.Id,
    cte.OrderId,
    cte.ItemId,
    cte.OrderDate,
    cte.[Lookup],
    cte.RowNumber
FROM ctE
INNER JOIN myrow
    ON ABS(cte.RowNumber - myrow.RowNumber) <= @rowsBeforeAndAfter
ORDER BY OrderDate, OrderId;

你可能想要CTE方法( 在结尾处看到一个原件 然而:

只需指出,这得到了正确的结果,但可能不是您所追求的,因为它依赖于行顺序,而项ID不是具有这两个值的实际行:

SELECT TOP  3
    a.Id,
    a.OrderId,
    a.ItemId,
    a.Lookup
FROM @OderDetail AS a
WHERE 
     a.ItemId = @ItemId

要解决这个问题,您可以使用 ORDER BY 和 TOP 1 用一个 UNION 有点难看。(更新日期排序和!=在ID上)

SELECT 
        u.Id,
        u.OrderId,
        u.OrderDate,
        u.ItemId,
        u.Lookup
FROM (
    SELECT 
        a.Id,
        a.OrderId,
        a.OrderDate,
        a.ItemId,
        a.Lookup
    FROM @OderDetail AS a
    WHERE 
         a.ItemId = @ItemId
         AND a.OrderId = @orderid
    UNION 
    SELECT top 1
        b.Id,
        b.OrderId,
        b.OrderDate,
        b.ItemId,
        b.Lookup
    FROM @OderDetail AS b
    WHERE 
         b.ItemId = @ItemId
         AND b.OrderId != @orderid
    ORDER BY b.OrderDate desc, b.OrderId
    UNION 
    SELECT top 1
        b.Id,
        b.OrderId,
        b.OrderDate,
        b.ItemId,
        b.Lookup
    FROM @OderDetail AS b
    WHERE 
         b.ItemId = @ItemId
        AND b.OrderId != @orderid
    ORDER BY b.OrderDate asc, b.OrderId 
) AS u
ORDER BY u.OrderDate asc, u.OrderId

Nirav Raval 7 年前

我认为这很简单,你可以用min(id)和max(id)来检查左外部连接或外部应用

喜欢

Declare @ItemID int = 2
Select * From @OderDetail A
Outer Apply (
    Select MIN(A2.Id) minID, MAX(A2.Id) maxID From @OderDetail A2
    Where A2.ItemId =@ItemID
) I05
Outer Apply(
    Select * From @OderDetail Where Id=minID-1
    Union All
    Select * From @OderDetail Where Id=maxID+1
    ) I052
Where A.ItemId =@ItemID Order By A.Id

如果这有助于你或你面对任何问题,请告诉我…

当做,