如何从db中选择version字段是最近的字段 [英] How to select from the db where version field is the recent one

问题描述

我有一个名为定价"的表，该表有很多字段，包括分销商"和版本".分发者字段是分发者表中的ID.每个发行商都有不同的版本.像这样的版本: 20131109AV-V1,20131110AV-V2,20131112AV-V3 是发行版= 1的版本，而 20131111WC-V1,20131111WC-V2 是发行版= 2的版本等等.我需要从定价表中选择所有数据，其中版本等于每个分销商的最新版本.

I have a table called 'pricing' which has so many fields including 'distributor' and 'version'. Distributor field is the id from the distributor table. Each distributor has different versions. Versions like: 20131109AV-V1,20131110AV-V2,20131112AV-V3 are the versions for distributor=1 AND 20131111WC-V1,20131111WC-V2 are the versions for distributor=2 etc. I need to select all the data from pricing table where version equals the recent version of each distributor.

例如:20131112AV-V3是发行商的最新版本= 1 20131111WC-V2是distributor = 2的最新版本

Eg: 20131112AV-V3 is the latest version of distributor=1 20131111WC-V2 is the latest version of distributor=2

我可以一步一步进行查询吗?

Can I make the query in one step?

还是我需要进行部分查询，例如，首先选择分发服务器和版本，然后使用分发服务器和最新版本在表中循环浏览(代码如下:)?

Or do I need to query part by part like, select distributor and version first, then loop through the table using distributor and latest version(code is given below:)?

$all_distributors=mysql_query("SELECT `distributor`,MAX(`version`) as `version` FROM `pricing` GROUP BY `distributor`",$con);
while($all_dist= mysql_fetch_array($all_distributors))
{
    $latest_version=$all_dist['version'];
    $distributor=$all_dist['distributor'];
    echo $distributor."-".$latest_version."<br/>";
        //Again select the query using this $distributor & $latest_version

}

有什么主意吗?

谢谢！

推荐答案

您可以为此使用单个查询:

you can use a single query for this:

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

这篇关于如何从db中选择version字段是最近的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！