MySQL 如何限制 LEFT JOIN 的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3348705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to limit results of a LEFT JOIN
提问by eddy edu
Take the case of two tables: tbl_productand tbl_transaction.tbl_productlists product details including names and ids while tbl_transactionlists transactions involving the products and includes dates, product-ids, customers etc.
以两个表为例：tbl_product和tbl_transaction。tbl_product列出产品详细信息，包括名称和 ID，同时tbl_transaction列出涉及产品的交易，包括日期、产品 ID、客户等。
I need to display a web-page showing 10 products and for each product, the last 5 transactions. So far, no LEFT JOINquery seems to work and the subquery below would have worked if mysql could allow the tx.product_id=ta.product_idpart (fails with Unknown column 'ta.product_id' in 'where clause': [ERROR:1054]).
我需要显示一个网页，其中显示 10 种产品以及每种产品的最后 5 笔交易。到目前为止，LEFT JOIN似乎没有任何查询有效，如果 mysql 可以允许该tx.product_id=ta.product_id部分，则下面的子查询将有效（失败，'where 子句'中的未知列 'ta.product_id': [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
Is there a way to achieve the listing I need without using multiple queries in a loop?
有没有办法在不循环使用多个查询的情况下实现我需要的列表？
Edit:
This is exactly what I need from MySQL:
编辑：
这正是我需要的 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
Of course this is illegal, but I really wish it wasn't!
当然这是非法的，但我真的希望它不是！
采纳答案by Thomas
This is where ranking functions would be very useful. Unfortunately, MySQL does not yet support them. Instead, you can try something like the following.
这是排名函数非常有用的地方。不幸的是，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
回答by Matthew
It fails because when you put parenthesis around your query and give it the alias "tb" you have created a derived table. Your derived table has no knowledge of the tbl_product table having alias "ta"
它失败是因为当您在查询周围加上括号并为其指定别名“tb”时，您已经创建了一个派生表。您的派生表不知道具有别名“ta”的 tbl_product 表
Try using ON or WHERE outside of the derived table, then reference that table using the alias" tb" you provided
尝试在派生表之外使用 ON 或 WHERE，然后使用您提供的别名“tb”引用该表
EDIT:
编辑：
The use of "LIMIT" limits the results of the query in its entirety. While you have "LIMIT 10" what you actually want is 50 rows (or less if there are fewer than 5 historical), is that right?
“LIMIT”的使用限制了整个查询的结果。虽然您有“LIMIT 10”，但您真正想要的是 50 行（如果历史记录少于 5 行，则更少），对吗？
10 products, joined to 5 historical records, returning 50 total rows.
10 个产品，加入 5 个历史记录，总共返回 50 行。
In this case, you can have a single query solution be joining the product table to itself in a derived table having "LIMIT 10"
在这种情况下，您可以使用单个查询解决方案将产品表连接到具有“LIMIT 10”的派生表中的自身
Such as:
如：
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
You could also us "in" and specify the top 10 such as:
您也可以“进入”并指定前 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)
回答by Ross
Edit:
编辑：
If you want to enforce a limit on each product I would separate it into two queries.
如果您想对每个产品实施限制，我会将其分为两个查询。
First get a list of 10 products and then run another query for each of those returning the last five transactions.
首先获取 10 个产品的列表，然后对返回最后五个交易的每个产品运行另一个查询。
回答by CETb
I found way to make LEFT JOIN with subquery, sorted and limited, its works on RDS Amazon, MariaDB v10.2
我找到了使用子查询进行 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
)
All others ways to make LEFT JOIN with GROUPING and SORTING didn't works for me. This example is working exception.
使用 GROUPING 和 Sorting 进行 LEFT JOIN 的所有其他方法对我都不起作用。这个例子是工作异常。
