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

每组组合的前n行

sql-server sql

tonyk · 技术社区 · 9 年前

通过以下输入,我想获得每个产品区域组合的前两行,按日期排序:

product  area  date        units
A        US    2015/08/07  10
A        US    2015/08/08  12
A        US    2015/08/09  15
A        UK    2015/08/07  13
A        UK    2015/08/10  11
B        US    2015/08/07  16
B        US    2015/08/08  17
B        US    2015/08/09  12
B        UK    2015/08/07  10
B        UK    2015/08/08  09
B        UK    2015/08/09  07

product  area  date        units
A        US    2015/08/07  10
A        US    2015/08/08  12
A        UK    2015/08/07  13
A        UK    2015/08/10  11
B        US    2015/08/07  16
B        US    2015/08/08  17
B        UK    2015/08/07  10
B        UK    2015/08/08  09

with ordered as (select product, area, date, units, 
                            row_number() over (partition by product, area order by date asc) as date_rank
                            from mytable)
                            select product, area, date, units
                            from ordered 
                            where date_rank <= 2

3 回复 | 直到 9 年前

SqlZim 9 年前

您的查询返回正确的结果。我看到的唯一可能从您的查询中遗漏的是一个 order by 为了你的期末考试 select

with ordered as (
  select product, area, date, units
    , row_number() over (partition by product, area order by date asc) as date_rank
  from mytable
)
select product, area, date, units
from ordered 
where date_rank <= 2
order by product, area desc, date_rank

rextester演示: http://rextester.com/XOMS68876

+---------+------+------------+-------+
| product | area |    date    | units |
+---------+------+------------+-------+
| A       | us   | 2015-08-07 |    10 |
| A       | us   | 2015-08-08 |    12 |
| A       | uk   | 2015-08-07 |    13 |
| A       | uk   | 2015-08-10 |    11 |
| B       | us   | 2015-08-07 |    16 |
| B       | us   | 2015-08-08 |    17 |
| B       | uk   | 2015-08-07 |    10 |
| B       | uk   | 2015-08-08 |     9 |
+---------+------+------------+-------+

Sefe 9 年前

CROSS APPLY

SELECT t1.product, t1.area, t2.date, t2.units
FROM (
    SELECT DISTINCT t1.product, t1.area
    FROM mytable
) AS t1
CROSS APPLY (
    SELECT TOP 2 date, units
    FROM mytable t2
    WHERE t1.product = t2.product and t1.area = t2.area
    ORDER BY date DESC
) AS t2

Randolph 9 年前

您可以使用 行编号() .

SELECT Product, Area, Date, Units
    FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY product, area ORDER By date ASC) AS xRowNumber FROM MyTable) xyz
        WHERE xRowNumber < 3