SQL查询以获取下一行的列为行的列，并将列的第二行按客户订购日期提供给下一行

I want to write a SQL query to get the column of the next row to be a column of a row. the test example is as follows

我想编写一个SQL查询,以使下一行的列成为一行的列。测试例如下

I want to get SQL query to get the result as follow:

我想获取SQL查询以获得如下结果:

1 个解决方案

#1

In newer versions of SQL Server, you can use the LEAD() window function, but prior to 2012, I believe the easiest way to achieve this is with outer applies:

在较新版本的SQL Server中,您可以使用LEAD()窗口函数,但在2012之前,我认为最简单的方法是使用外部适用:

DECLARE @T TABLE (Customer CHAR(1), [Date] DATE, Item VARCHAR(10));

INSERT @T VALUES
('A', '2016-01-01', 'Pen A'),
('A', '2016-01-05', 'Pen B'),
('A', '2016-01-06', 'Pen C'),
('A', '2016-01-07', 'Pen D'),
('B', '2016-01-01', 'Pen A'),
('B', '2016-01-05', 'Pen B'),
('B', '2016-01-06', 'Pen C'),
('B', '2016-01-07', 'Pen IS');

SELECT T.Customer,
       T.FirstOrderedDate,
       T.FirstOrderedItem,
       T.SecondOrderedDate,
       T.SecondOrderedItem,
       ThirdOrderedDate = T3.[Date],
       ThirdOrderedItem = T3.Item
FROM
(
    SELECT T1.Customer,
           FirstOrderedDate = T1.[Date],
           FirstOrderedItem = T1.Item,
           SecondOrderedDate = T2.[Date],
           SecondOrderedItem = T2.Item
    FROM @T AS T1
    OUTER APPLY
    (
        SELECT TOP 1 T2.Customer, T2.[Date], T2.Item
        FROM @T AS T2
        WHERE T2.Customer = T1.Customer
        AND T2.[Date] > T1.[Date]
        ORDER BY T2.[Date]
    ) AS T2
) AS T
OUTER APPLY
(
    SELECT TOP 1 T3.Customer, T3.[Date], T3.Item
    FROM @T AS T3
    WHERE T3.Customer = T.Customer
    AND T3.[Date] > T.SecondOrderedDate
    ORDER BY T3.[Date]
) AS T3;

Note: Seeing as you haven't really given any specifics about a customer ordering different items on the same date, I've just assumed that each customer has ordered only one thing on a given date, but if that's not the case, then you'd have to adjust the query logic slightly.

注意:由于您没有真正说明客户在同一天订购不同商品的任何细节,我只是假设每个客户在指定日期仅订购了一件商品,但如果不是这样,那么您我必须稍微调整查询逻辑。