Подзапитване и избиране на най-старите редове за множество външни ключове в MySQL

Имам две маси:

product (idproduct, name, description, tax)

product_storage (idstorage, idproduct, added, quantity, price)

за всеки продукт може да е различна цена и най-старите са "първи за продажба"

например в хранилището имам:

1, 1, 2010-01-01,  0, 10.0
2, 1, 2010-01-02,  0, 11.0
3, 1, 2010-01-03, 10, 12.0
4, 2, 2010-01-04,  0, 12.0
5, 2, 2010-01-05, 10, 11.0
6, 2, 2010-01-06, 10, 13.0
7, 3, 2010-01-07, 10, 14.0
8, 3, 2010-01-08, 10, 16.0
9, 3, 2010-01-09, 10, 13.0

и сега трябва да избера всички продукти с текуща цена, която е в най-стария ред в product_storage, където количеството е > 0 за всеки продукт:

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price
FROM product p;

работи добре, но не, когато искам да изчисля цена с данък в заявката:

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (price * (1 + tax/100)) AS price_with_tax
FROM product p;

MySQL казва:

Unknown column 'price' in 'field list'

Актуализация

Използването на подзаявка като таблица почти решава проблема (вижте отговорите) - единственият въпрос сега е как да изберете най-старите редове от product_storage за множество външни ключове (един и само един за всеки idproduct).

Актуализация 2

Благодаря на cmptrgeekken за страхотното решение :))


mysql sql subquery
person Marek    schedule 09.03.2010    source източник

Отговори (5)


arrow_upward
1
arrow_downward

Причината да получавате грешката price doesn't exist е, че не можете да препращате към колони с псевдоними в списъка с колони. За да коригирате това, запазете стойността price в дефинирана от потребителя променлива и препратете към нея, така:

SELECT p.idproduct, p.name, p.tax,
       @price := (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (@price * (1 + tax/100)) AS price_with_tax
FROM product p;
person cmptrgeekken    schedule 09.03.2010

arrow_upward
2
arrow_downward

Това може да работи по-добре, като се използва подзаявката като таблица:

SELECT p.idproduct, p.name, p.tax, s.price, (s.price * (1 + p.tax/100)) as price_with_tax
FROM product p
INNER JOIN (SELECT idproduct, price
        FROM product_storage
        WHERE quantity > 0) s
ON p.idproduct = s.idproduct
INNER JOIN (SELECT idproduct, MIN(added) min
        FROM product_storage
        GROUP BY idproduct) f
ON s.idproduct = f.idproduct AND s.added = f.min

Редактиране: Актуализирано, защото данъкът е в другата таблица, така че трябваше да преместя местоположението на това изчисление.

Редактиране 2: Добре, промених нещата отново, за да опитам да филтрирам правилно таблицата product_storage.

person Powerlord    schedule 09.03.2010
comment
Неизвестна колона „данък“ в „списък с полета“ - person Marek; 09.03.2010
comment
може би трябва да денормализирам таблиците и да повторя данъчните данни в product_storage? - person Marek; 09.03.2010
comment
@Marek: Ооо, данъкът е в другата таблица, нали? - person Powerlord; 09.03.2010
comment
да... но пак не е наред - с LIMIT 1 подзаявка е таблица с 1 ред, а без нея има всички редове WHERE количество › 0... по-специално има повече от 1 ред за всеки продукт (с различни цени) - виж първия ми отговор на този въпрос ;) - person Marek; 09.03.2010
comment
@Marek: Трябваше да се сетя за това. Възможно е да се поправи, но заявката може да стане по-сложна. - person Powerlord; 09.03.2010

arrow_upward
1
arrow_downward

това работи почти добре:

SELECT p.idproduct, p.name, p.tax,
       sub.price, (sub.price * (1+tax/100)) as price_with_tax
FROM product p,
    (SELECT s.idproduct, s.price
     FROM product_storage s
     WHERE quantity > 0
     ORDER BY added ASC) sub
WHERE p.idproduct=sub.idproduct

но всички редове от product_storage се връщат за всеки продукт:/

person Marek    schedule 09.03.2010

arrow_upward
0
arrow_downward

Що се отнася до MySQL, последното срещане на думата "price" се отнася до поле в Product p, оттук и грешката. Трябва да се обърнете отново към подзаявката с псевдоним точно над това:

SELECT p.idproduct, p.name, p.tax,
       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) AS price,
        (       (SELECT s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC
        LIMIT 1) * (1 + tax/100)) AS price_with_tax
FROM product p;
person LesterDove    schedule 09.03.2010
comment
в таблицата „продукт“ няма поле „цена“ – сигурни ли сте, че тази повтаряща се подзаявка не се изчислява отново? - person Marek; 09.03.2010
comment
Това беше точката, която не успях да изясня в поста си. Не можете да използвате полето с псевдоним, за да изчислите данъчната стойност, защото MySQL търси колона „цена“ в таблицата с продукти. Така че това, което написах, изрично изисква отново изчислената стойност от Product_Storage. Във всеки случай мисля, че отговорът на PowerLord е напълно по-добър (разберете.) - person LesterDove; 09.03.2010

arrow_upward
0
arrow_downward

Първо, искате да получите най-ниския идентификатор за съхранение за продукта(ите).

SELECT idproduct, MIN(idstorage) idstorage FROM product_storage 
WHERE quantity>0 AND idproduct IN (#, #, ...)
GROUP BY idstorage

idstorage, idproduct, added, quantity, price След това можете да се присъедините към тази заявка и таблицата product_storage (отново), като използвате ID за минимално съхранение, за да получите информация за вашия продукт с правилната информация за цените.

SELECT idproduct, name, description, tax, ps.price, ps.quantity,
FROM product AS p
JOIN 
(SELECT idproduct, MIN(idstorage) idstorage FROM product_storage 
WHERE quantity>0 AND idproduct IN (#, #, ...)
GROUP BY idstorage) AS min_id
ON p.idproduct=min_id.idproduct
RIGHT JOIN product_storage AS ps ON ps.idstorage=min_id.idstorage
WHERE idproduct IN (#, #, ...)
person Brent Baisley    schedule 09.03.2010
comment
... idproduct IN (#, #, ...) ... - idproduct не е посочен - например вземам най-новите продукти, продукти от категория X и т.н. - person Marek; 09.03.2010