Подзапрос и выбор самых старых строк для нескольких внешних ключей в 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 строкой и без нее все строки ГДЕ количество › 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, то последнее вхождение слова «цена» относится к полю в 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. Итак, то, что я написал, снова явно запрашивает вычисленное значение из 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, добавлено, количество, цена Затем вы можете присоединиться к этому запросу и таблице product_storage (снова), используя минимальный идентификатор хранилища, чтобы получить информацию о вашем продукте с правильной информацией о ценах.

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