关于求和后排序的sql问题

Question

我有一个产品表，其中包含一次的所有销售数量..所以该表是：

id |产品部门 ID |产品 ID | amount_sold

我需要列出所有product_department_ids 中最畅销的 2 个产品。我有什么想法可以这样做吗？

如果你能在 pl/sql 中做到这一点那就太好了，但 sql 也可以！

谢谢 ！

Answer 1

drop table quantity;

create table quantity (
  id number primary key,
  product_department_id number,
  product_id number,
  quantity_sold number,
  unique (product_department_id, product_id)
);

insert into quantity values (1, 1, 1, 10);
insert into quantity values (2, 1, 2, 20);
insert into quantity values (3, 1, 3, 30);

insert into quantity values (4, 2, 1, 60);
insert into quantity values (5, 2, 2, 50);
insert into quantity values (6, 2, 3, 40);


select * from (
 select quantity_sold, product_id, product_department_id,
       row_number() over (partition by product_department_id order by quantity_sold desc) r
  from quantity
) where r < 3;

编辑仍然不确定到底问了什么，但如果组合产品/部门可以有多个条目，那么它将是：

drop table quantity;

create table quantity (
  id number primary key,
  product_department_id number,
  product_id number,
  quantity_sold number
);

insert into quantity values ( 1, 1, 1, 15);
insert into quantity values ( 2, 1, 1, 15);
insert into quantity values ( 3, 1, 1, 15);
insert into quantity values ( 4, 1, 2, 20);
insert into quantity values ( 5, 1, 3, 30);

insert into quantity values (10, 2, 1, 60);
insert into quantity values (11, 2, 2, 50);
insert into quantity values (12, 2, 3, 40);
insert into quantity values (13, 2, 3, 30);


select * from (
 select sum(quantity_sold), 
        product_id, product_department_id,
        row_number() over (partition by product_department_id 
                           order by sum(quantity_sold) desc
        ) r
  from  quantity
  group by product_department_id, product_id
) where r < 3
order by product_department_id, product_id;

Answer 2

如果一种产品只能有一个部门，您只需order by即可：

select  product_department_id
from    YourTable
where   rownum < 3
order by
        quantity_sold desc