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