У меня есть 3 таблицы MySQL:
items
id | title
1 | The Matrix DVD
2 | Star Wars DVD
itemListings
// condition_id 1 = New, 2 = Used
id |item_id| condition_id
1 | 2 | 2
2 | 2 | 1
3 | 2 | 1
4 | 2 | 1
5 | 2 | 2
6 | 2 | 2
8 | 2 | 2
9 | 1 | 1
itemListings_inventory
id | listing_id | start_price
1 | 1 | 4.00
2 | 2 | 12.00
3 | 3 | 14.00
4 | 4 | 15.00
5 | 5 | 6.00
6 | 6 | 7.00
7 | 8 | 11.00
8 | 8 | 9.00
9 | 8 | 2.00
10 | 8 | 13.00
11 | 9 | 5.00
Если пользователь ищет "звездные войны", мне нужно вернуть:
item_id | title | lowest_new_price | lowest_used_price
2 | Star Wars DVD | 12.00 | 2.00
Пока у меня есть этот SQL, который возвращает только low_used_price:
SELECT
items.id,
items.title,
MIN(start_price) AS lowest_used_price
FROM itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
JOIN items ON itemListings.item_id = items.id
WHERE itemListings.condition_id = 2
AND MATCH (items.title, items.description) AGAINST ('star wars')
GROUP BY items.id
но как я могу получить lower_new_price и lower_used_price?
ОБНОВЛЕНИЕ: Некоторые предметы будут иметь как новую цену, так и использованную цену, некоторые предметы будут иметь только новую цену, а некоторые предметы будут иметь только использованную цену.
Мысль:
Ваша таблица товаров, она содержит старые и новые предметы, не так ли? И ваш запрос по сути состоит в том, как вы получаете минимально используемую и минимальную новую цену.
Не могли бы вы сделать это, если используемые элементы были в одной таблице, а новые элементы были в другой таблице (так что две отдельные таблицы списков вместо вашей)
Наверное..
Таким образом, ответ заключается в том, чтобы концептуально разделить эту таблицу списков на две части, дважды помещая их в свой запрос, один раз для нового, один раз на старую. Это проще всего понять с помощью подзапросов:
SELECT * FROM
Items
INNER JOIN
(
SELECT
ItemListings.id,
MIN(start_price) AS lowest_used_price
FROM
itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
WHERE itemListings.condition_id = 2
GROUP BY items.id
)used
ON items.id = used.id
INNER JOIN
(
SELECT
items.id,
MIN(start_price) AS lowest_new_price
FROM
itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
WHERE itemListings.condition_id = 1
GROUP BY items.id
) nu
ON items.id = nu.id
Вы также можете сделать это с помощью метода, называемого pivoting, где вы его присоединяете один раз, но используйте условные выражения, чтобы выделять только интересные строки, а затем группировать их:
SELECT
items.id,
items.title,
MIN(CASE WHEN condition_id = 2 THEN start_price END) AS lowest_used_price,
MIN(CASE WHEN condition_id = 1 THEN start_price END) AS lowest_new_price
FROM
itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
JOIN items ON itemListings.item_id = items.id
GROUP BY items.id, items.title
Чтобы узнать больше о том, как это работает, выполните следующие действия:
SELECT items.id, items.title,
CASE WHEN condition_id = 2 THEN start_price END AS used_price,
CASE WHEN condition_id = 1 THEN start_price END AS new_price
FROM
itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
JOIN items ON itemListings.item_id = items
И знайте, что MIN выбирает наименьшее ненулевое значение
Заметьте, я не поставил никаких WHERE item.id = 2
или WHERE items.title LIKE '%star wars%'
(т.е. только строки "Звездных войн") в этих запросах; Я решил, что вы можете сделать эту часть самостоятельно (и в любом случае вряд ли вы захотите жестко обозначить Star Wars как единственный вид DVD-кода, который ищет ваш код); это больше связано с прохождением базовой методики присоединения к таблице в два раза, если у нее есть вещи, которые вы хотите отобразить в одной строке результатов, хотя таблица имеет их в разных строках.. а также о другой технике использования CASE КОГДА уменьшите неинтересные строки до нуля, так что MIN будет игнорировать их.
Групповые решения предпочтительнее для меня, чтобы быть несколько более общим. Я бы сделал что-то вроде этого:
SELECT x.id, x.title, SUM(IF(x.conditionId = 1, x.price, 0)) lowest_new_price, SUM(IF(x.conditionId = 2, x.price, 0)) lowest_used_price
( SELECT i.id, i.title, iL.conditionId, MIN(iLI.start_price) price FROM items i, itemListings iL, itemListings_inventory iLI
WHERE i.title = 'Star Wars'
AND i.id = iL.id
AND iL.id = iLI.listing_id
GROUP BY i.id, i.title, iL.conditionId) x
GROUP BY i.id, i.title
Условные суммы выбирают минимальную цену групп conditon_id из подзапроса.
Это может быть более эффективным, но это должно сработать.
SELECT new.id, new.title, new.lowest_new_price, used.lowest_used_price
FROM (
SELECT
items.id,
items.title,
MIN(start_price) AS lowest_new_price
FROM itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
JOIN items ON itemListings.item_id = items.id
WHERE itemListings.condition_id = 1
AND MATCH (items.title, items.description) AGAINST ('star wars')
GROUP BY items.id
) new
JOIN (
SELECT
items.id,
items.title,
MIN(start_price) AS lowest_used_price
FROM itemListings_inventory
JOIN itemListings ON itemListings_inventory.listing_id = itemListings.id
JOIN items ON itemListings.item_id = items.id
WHERE itemListings.condition_id = 2
AND MATCH (items.title, items.description) AGAINST ('star wars')
GROUP BY items.id
) used ON used.id = new.id
FULL JOIN
присоединиться к нему. MySQL не поддерживает FULL JOIN, поэтому вам понадобится (SELECT ... FROM ... LEFT JOIN ...) UNION ALL (SELECT ... FROM ... RIGHT JOIN ...)