php - JOIN 和 INNER JOIN 查询

Question

注意:我之前问过一个问题(How to select from the db where version field is the recent one)，效果很好。这不是重复的。

SELECT p.* FROM `pricing` p 
JOIN (SELECT `distributor`,MAX(`version`) AS ver 
      FROM `pricing` GROUP BY `distributor`) mx 
  ON mx.ver=p.version AND p.distributor = mx.distributor

使用上述查询，我​​可以从定价表中选择经销商拥有最新版本的所有详细信息。[经销商将有不同的版本。我只需要选择最新版本]。我有另一个名为product_picker 的表。我只需要从定价表中选择数据，其中定价代码= Product_picker 代码。我使用了以下代码:

SELECT p.* FROM `pricing` p,pk.* 
FROM `product_picker` 
JOIN (SELECT `distributor`,MAX(`version`) AS ver 
      FROM `pricing` GROUP BY `distributor`) mx 
  ON mx.ver=p.version AND p.distributor = mx.distributor 
INNER JOIN `product_picker` pk ON p.code=pk.code

但我知道这不会很好地工作。我刚刚学习 JOIN 子句。如何改进我的代码？

谢谢!

Answer 1

机制非常简单:

SELECT p.*, pk.*
FROM 
    `pricing` p 
        JOIN (
            SELECT 
                `distributor`,
                MAX(`version`) AS ver 
            FROM `pricing` 
            GROUP BY `distributor`
        ) mx ON mx.ver = p.version AND p.distributor = mx.distributor
        JOIN `product_picker` pk ON pk.code = p.code -- the join has to be added after the other one, and the ON clause has to make it clear for SQL that the code is the criteria