postgresql - PostgreSQL 按组限制，仅显示前 2 个存储选项

Question

我需要选择前 2 行，其中 store_name 与给定产品的不同

 id | store_name | prod_name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | A
  5 |          2 | E
  6 |          2 | A
  7 |          3 | G
  8 |          2 | A
  9 |          1 | A
 10 |          3 | A
(10 rows)

结果应该是 store_name <> 3 AND prod_name ='A'

 id | store_name | prod_name
----+------------+------
  1 |          1 | A
  4 |          1 | A
  6 |          2 | A
  8 |          2 | A

标签： postgresql

Answer 1

使用row_number()窗口函数来完成此操作。

查询 #1

    with first_two as (
      select *, 
             row_number() over (partition by store_name
                                    order by id) as rn
        from store_product
       where store_name <> 3
         and prod_name = 'A'
    )
    select id, store_name, prod_name
      from first_two
     where rn <= 2;

| id  | store_name | prod_name |
| --- | ---------- | --------- |
| 1   | 1          | A         |
| 4   | 1          | A         |
| 6   | 2          | A         |
| 8   | 2          | A         |

在 DB Fiddle 上查看