如何限制左联接的结果 [英] How to limit results of a LEFT JOIN

问题描述

以两个表为例:tbl_product和tbl_transaction.
tbl_product列出产品详细信息，包括名称和ID，而tbl_transaction列出涉及产品的交易，包括日期，产品ID，客户等.

Take the case of two tables: tbl_product and tbl_transaction.
tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc.

我需要显示一个网页，其中显示10个产品，每个产品的最近5个交易.到目前为止，似乎没有任何LEFT JOIN查询有效，并且如果mysql允许tx.product_id=ta.product_id部分，则下面的子查询也可以工作(在"where子句"中出现 Unknown列'ta.product_id'失败:[ERROR:1054 ] ).

I need to display a web-page showing 10 products and for each product, the last 5 transactions. So far, no LEFT JOIN query seems to work and the subquery below would have worked if mysql could allow the tx.product_id=ta.product_id part (fails with Unknown column 'ta.product_id' in '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

是否有一种方法可以实现我需要的列表，而无需循环使用多个查询?

Is there a way to achieve the listing I need without using multiple queries in a loop?

这正是我从MySQL需要的:

This is exactly what I need from 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!

推荐答案

这是排名函数非常有用的地方.不幸的是，MySQL尚不支持它们.相反，您可以尝试类似以下的方法.

This is where ranking functions would be very useful. Unfortunately, MySQL does not yet support them. Instead, you can try something like the following.

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

这篇关于如何限制左联接的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！