如何限制 LEFT JOIN 的结果

2022-01-23 00:00:00 sql left-join subquery mysql mysql-error-1054

以两个表为例: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 子句"中的未知列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

相关文章