Как ограничить результаты LEFT JOIN
Возьмем случай с двумя таблицами: tbl_product
и tbl_transaction
.
tbl_product
перечисляет сведения о продуктах, включая имена и идентификаторы, а tbl_transaction
перечисляет транзакции с продуктами и включает даты, идентификаторы продуктов, клиентов и т.д.
Мне нужно отобразить веб-страницу, показывающую 10 продуктов и для каждого продукта, последние 5 транзакций. Пока что запрос LEFT JOIN
не работает, и подзапрос ниже работал бы, если бы mysql мог разрешить часть tx.product_id=ta.product_id
(с ошибкой Неизвестный столбец "ta.product_id" в "where clause": [ERROR: 1054 ]).
SELECT
ta.product_id,
ta.product_name,
tb.transaction_date
FROM tbl_product ta
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx WHERE tx.product_id=ta.product_id LIMIT 5) tb
LIMIT 10
Есть ли способ достичь листинга, мне нужно без использования нескольких запросов в цикле?
Edit:
Это именно то, что мне нужно от MySQL:
SELECT ta.product_id, ta.product_name, tb.transaction_date ...
FROM tbl_product ta
LEFT JOIN tbl_transaction tb ON (tb.product_id=ta.product_id LIMIT 5)
LIMIT 10
Конечно, это незаконно, но я действительно хочу, чтобы это не было!
Ответы
Ответ 1
Здесь функции ранжирования будут очень полезны. К сожалению, MySQL еще не поддерживает их. Вместо этого вы можете попробовать что-то вроде следующего.
Select ta.product_id, ta.product_name
, tb.transaction_date
From tbl_product As ta
Left Join (
Select tx1.product_id, tx1.transaction_id, tx1.transaction_date
, (Select Count(*)
From tbl_transaction As tx2
Where tx2.product_id = tx1.product_id
And tx2.transaction_id < tx1.transaction_id) As [Rank]
From tbl_transaction As tx1
) as tb
On tb.product_id = ta.product_id
And tb.[rank] <= 4
Limit 10
Ответ 2
Не удается, потому что, когда вы помещаете круглые скобки вокруг своего запроса и даете ему псевдоним "tb", вы создали производную таблицу.
Ваша производная таблица не знает таблицу tbl_product с псевдонимом "ta"
Попробуйте использовать ON или WHERE вне производной таблицы, затем ссылайтесь на эту таблицу, используя псевдоним "tb", который вы предоставили
EDIT:
Использование "LIMIT" ограничивает результаты запроса в целом.
Хотя у вас есть "LIMIT 10", то, что вы на самом деле хотите, составляет 50 строк (или меньше, если их меньше 5), верно?
10 продуктов, соединенных с 5 историческими записями, возвращая 50 полных строк.
В этом случае вы можете иметь единственное решение для запроса, соединяющее таблицу продуктов с самим собой в производной таблице с "LIMIT 10"
Например:
SELECT *
FROM tbl_product ta
JOIN (SELECT * FROM tbl_product tz WHERE tz.product_id = ta.product_id LIMIT 10) tc
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx
WHERE tx.product_id=ta.product_id LIMIT 5) tb
Вы также можете "войти" и указать 10 лучших, таких как:
SELECT *
FROM tbl_product ta
LEFT JOIN (SELECT tx.transaction_date FROM tbl_transaction tx
WHERE tx.product_id=ta.product_id LIMIT 5) tb
WHERE ta.product_id IN
(SELECT z.product_id FROM tbl_product z LIMIT 10)
Ответ 3
Edit:
Если вы хотите обеспечить ограничение для каждого продукта, я бы разделил его на два запроса.
Сначала получите список из 10 продуктов, а затем запустите другой запрос для каждого из тех, кто возвращает последние пять транзакций.
Ответ 4
Я нашел способ сделать LEFT JOIN с подзапросом, отсортированным и ограниченным, он работает на RDS Amazon, MariaDB v10.2
LEFT JOIN activities last_activity ON last_activity.id = (
SELECT id FROM activities
WHERE contester_id = contesters.id AND 'type' IN ({$last_activity_types})
ORDER BY id DESC LIMIT 1
)
Ответ 5
Вам нужно вытащить условие соединения из вашего подзапроса. Попробуйте:
SELECT
ta.product_id,
ta.product_name,
tb.transaction_date
FROM tbl_product ta
LEFT JOIN (SELECT tx.transaction_date
FROM tbl_transaction tx
ORDER BY tx.transaction_date DESC
LIMIT 5) tb
ON ta.product_id=tb.product_id
LIMIT 10