在t-sql中获取第二行作为结果表 [英] Get every second row as a result table in t-sql

问题描述

我正在寻找一个返回列表的t-sql脚本，该列表显示来自Table1分组的每个第二个值.

I'm looking for a t-sql script that returns a list, that shows every second value from a grouping from Table1.

例如，我有以下数据(表1)，并想要所需的结果列表:

For example I have the following data (Table1) and want the desired result-list:

Table1:
Customer    Quantity
A           5
A           8               (*)
B           3
B           5                (*)
B           11
C           7
D           4
D           23               (*)

所需的引文列表:

Customer    Quantity
A           8
B           5
D           23

我考虑使用选择不同的和左外部连接"来做某事，但是我无法使其正常工作.可能我需要一个行编号，但无法弄清楚该怎么做.有人可以帮助我吗?

I think about doing something something with 'select distinct and left outer join', but I can't get it to work. Possibly I need an row numbering, but can't figure out how to do it. Anyone can help me?

下面是我用来制作和填充Table1的脚本:

Beneath is the script I used to make and fill Table1:

CREATE TABLE Table1
(Customer nvarchar(1) NULL,
Quantity int NOT NULL);

INSERT INTO Table1(Customer,Quantity)
VALUES 
('A',5),
('A',8),
('B',3),
('B',5),
('B',11),
('C',7),
('D',4),
('D',23);

推荐答案

您可以使用ROW_NUMBER和CTE:

WITH data AS (
    SELECT  *, ROW_NUMBER() OVER  (PARTITION BY Customer ORDER BY Quantity) rn
    FROM    @Table1 
)
SELECT Customer, Quantity
FROM    data 
WHERE   rn = 2

工作原理:

使用ROW_NUMBER()将根据OVER ()中指定的内容为每行分配一个顺序号.在OVER中，我指定PARTITION在客户上的行，这意味着将对同一客户上的每组数据分别进行编号.然后ORDER BY Quantity表示它应该根据每个客户的数量订购数据-这样我就可以按数量订购每个客户的第二行.

Using ROW_NUMBER() will assign a sequential number to each row based on what's specified in OVER (). In OVER i specify to PARTITION the rows on customer, that means each group of data on same customer will be numberered separately. Then ORDER BY Quantity mean it should order the data based on quantity for each customer - so i can get the 2nd row for each customer ordered by quantity.

这篇关于在t-sql中获取第二行作为结果表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！