Как выбрать товар с минимальной ценой из группы категорий и производителей?

У меня есть проблема, которая выглядит тривиальной, но я не могу заставить ее работать. Это в Oracle SQL. Вот пример скрипта:

create table product (
product_id number primary key,
name varchar(255)
);

create table producer (
producer_id number primary key,
name varchar(255)
);

create table catalog (
pp_product_id number,
pp_producer_id number,
price number
);


alter table catalog add constraint pp_product_id1 foreign key (pp_product_id) references product (product_id);
alter table catalog add constraint pp_product_id2 foreign key (pp_producer_id) references producer (producer_id);


insert into product (product_id, name) values (1, 'HDD 250 gb');
insert into product (product_id, name) values (2, 'HDD 500 gb');
insert into product (product_id, name) values (3, 'HDD 750 gb');

insert into producer (producer_id, name) values (1, 'Hitachi');
insert into producer (producer_id, name) values (2, 'Corsair');
insert into producer (producer_id, name) values (3, 'Western Digital');

insert into catalog (pp_product_id, pp_producer_id, price) values (1,1, 80);
insert into catalog (pp_product_id, pp_producer_id, price) values (1,3, 60);
insert into catalog (pp_product_id, pp_producer_id, price) values (2,1, 75);
insert into catalog (pp_product_id, pp_producer_id, price) values (2,2, 40);
insert into catalog (pp_product_id, pp_producer_id, price) values (3,2, 63);
insert into catalog (pp_product_id, pp_producer_id, price) values (3,3, 100);

Итак, всего 6 товаров, 3 производителя, 3 категории товаров (видов HD). Мне нужно отобразить САМЫЙ ДЕШЕВЫЙ продукт для каждого типа продукта с именем производителя, связанным с этим продуктом. Что-то типа:

Вестерн Диджитал 250 гб 60

Корсар 500 гб 40

Корсар 750 гб 63

Это выберет самый дешевый HD в своей группе (тип)

select p.name, min (c.price)
from product p, catalog c, producer prc
where c.pp_product_id = p.product_id and prc.producer_id = c.pp_producer_id
group by p.name;

...но я не могу добавить имя производителя? Я пробовал несколько решений, но ни одно из них не работает.


sql function oracle min
person Nenad Bulatovic    schedule 25.03.2013    source источник
comment
Вы не можете добавить имя производителя, потому что оно не является частью группы   -  person evgenyl    schedule 25.03.2013

Ответы (4)


arrow_upward
2
arrow_downward

Это хорошее применение для row_number(). В ваших данных нет поля type, поэтому я придумал его в таблице продуктов:

select *
from (select p.name as product_name, p.type, prod.name as producer_name,
             price,
             ROW_NUMBER() over (partition by p.name order by price ) as seqnum
      from catalog c join
           product p
           on c.pp_product_id = p.product_id join
           producer prod
           on c.pp_producer_id = prod.producer_id
     ) t
where seqnum = 1;

Если вы хотите все продукты по минимальной цене, используйте этот небольшой вариант:

select *
from (select p.name as product_name, p.type, prod.name as producer_name,
             price,
             min(price) over (partition by p.name) as minprice
      from catalog c join
           product p
           on c.pp_product_id = p.product_id join
           producer prod
           on c.pp_producer_id = prod.producer_id
     ) t
where price = minprice;
person Gordon Linoff    schedule 25.03.2013
comment
Он говорит: Ошибка SQL: ORA-00904: PR.PRODUCER_ID: неверный идентификатор 00904. 00000 - %s: неверный идентификатор? - person Nenad Bulatovic; 25.03.2013
comment
На самом деле название продукта, такое как HDD 250 ГБ, следует рассматривать как тип - person Nenad Bulatovic; 25.03.2013
comment
С небольшим изменением решение: выберите * из (выберите p.name как product_name, prod.name как product_name, price, ROW_NUMBER() over (partition by p.name order by price) как seqnum из каталога c, присоедините продукт p к c. pp_product_id = p.product_id присоединиться к производителю prod on c.pp_producer_id = prod.producer_id ) t где seqnum = 1; - person Nenad Bulatovic; 25.03.2013
comment
Если я применю это решение: выберите * из (выберите p.name как product_name, prod.name как product_name, price, ROW_NUMBER() over (partition by p.name order by price) как seqnum из каталога c, присоедините продукт p к c. pp_product_id = p.product_id присоединиться к производителю prod on c.pp_producer_id = prod.producer_id ) t где seqnum = 1; результат в порядке, но только если нет двух (или более) товаров с одинаковой (минимальной) ценой. Как мне это решить? - person Nenad Bulatovic; 26.03.2013

arrow_upward
2
arrow_downward

Это должно ответить на ваш основной вопрос. В сообщении рассматривается проблема в MySQL, но применяется та же концепция.

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Как только вы получаете продукт с минимальной ценой в группе, остальные просто присоединяются.

person Daniel Williams    schedule 25.03.2013

arrow_upward
1
arrow_downward

Спасибо @Gordon Linoff - с небольшим изменением его кода это работает для моей проблемы. Просто для ясности я публикую решение здесь:

select *
from (select p.name as product_name, prod.name as producer_name,price,
ROW_NUMBER() over (partition by p.name order by price ) as seqnum
from catalog c join
product p
on c.pp_product_id = p.product_id join
producer prod
on c.pp_producer_id = prod.producer_id
) t
where seqnum = 1;
person Nenad Bulatovic    schedule 25.03.2013

arrow_upward
0
arrow_downward

Тем временем я нашел еще один возможный ответ:

SELECT p.name, pr.name, c2.minprice
FROM product p, producer pr, catalog c,
  (SELECT pp_product_id, MIN(price) AS minprice
    FROM catalog c1
    GROUP BY pp_product_id
  ) c2
WHERE p.product_id=c.pp_product_id
AND pr.producer_id=c.pp_producer_id
AND p.product_id  =c2.pp_product_id
AND c.price       =c2.minprice;

Вывод такой:

Жесткий диск 750 Гб Corsair 6300

Жесткий диск 500 Гб Corsair 4000

Жесткий диск 250 Гб Western Digital 6000

person Nenad Bulatovic    schedule 26.03.2013