加入还是SubQuery？

Question

我有一个MySQL表，它记录了在各种网站上使用的插件标题，包括版本号。例如：

    AddonName | Website ID | Version

    ZZZ         1           3.3
    ZZZ         2           3.4
    ZZZ         3           3.4
    ZZZ         4           3.1
    YYY         1           1.1
    YYY         2           1.1
    YYY         3           1.1
    YYY         4           1.2

我想要创建一个查询，其中列出了一个不同的AddonName列表，其中包括总计数、使用最新版本的所有站点的计数以及使用过期版本的所有站点的计数。

即：

    Name | Total Addons | Up to Date | Out of Date
    ZZZ    4              2            2
    YYY    4              1            3

我不知道如何得到这种类型的数据返回，即使信息都在那里。我尝试使用JOIN查询，但没有成功。

如果这有助于简化操作，我可以在表中添加一个‘最新’枚举字段，在导入时将行标记为最新的或过时的。

Answer 1

假设最大值为最新版本。

试试这个：

select t1.AddonName,
count(*) as total_Addon,
sum(case when t1.version=t2.version then 1 else 0 end) as up_to_date,
sum(case when t1.version!=t2.version then 1 else 0 end) as out_of_date
from table1 t1
inner join(
  select AddonName,max(version) as version
  from table1 group by AddonName
)t2 on t1.AddonName=t2.AddonName 
group by t1.AddonName

Answer 2

尝试：

SELECT your_table.AddonName,
   COUNT(`Website ID`),
   COUNT(IF(Version = your_table_max.max_version, 1, NULL)) AS `Up to Date`,
   COUNT(IF(Version <> your_table_max.max_version, 1, NULL)) AS `Out of Date`
  FROM your_table
    INNER JOIN (SELECT MAX(Version) as max_version, AddonName
                  FROM your_table group by AddonName) your_table_max
    ON your_table_max.AddonName = your_table.AddonName
  GROUP BY your_table.AddonName;

Answer 3

假设最新版本来自上一栏：

select t.name, count(*) as TotalAddons,
       sum(t.version = tt.maxv) as UpToDate,
       sum(t.version <> tt.maxv) as OutOfDate
from t join
     (select name, max(version) as maxv
      from t
      group by name
     ) tt
     on t.name = tt.name
group by t.name;

这将计算子查询中每个名称的最大版本号。然后，它将该信息用于外部聚合。

这假设版本是一个数字。如果它是一个字符串(所以1.10 > 1.2)，那么类似的方法是：

select t.name, count(*) as TotalAddons,
       sum(t.version = t.maxv) as UpToDate,
       sum(t.version <> t.maxv) as OutOfDate
from (select t.*,
             (select version
              from t t2
              where t2.name = t.name
              order by length(version) desc, version desc
              limit 1
             ) as maxv
      from t
     ) t
group by t.name;

当然，这也适用于数字。