加入或子查询？

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查询，但没有任何成功。

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

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; 

当然，这也将为数字正常工作。

Answer 4

一旦你添加Latest表，请尝试以下（插入你的表名之后）

select 
    AddonName as Name, 
    count(*) as TotalAddons, 
    count(case TableName.Version when Latest.LatestVersion 
    then 1 else null end) as UpToDate, 
    TotalAddons-UpToDate as OutOfDate 

from TableName join Latest 
    on TableName.AddonName = Latest.AddonName 
group by AddonName 

Answer 5

试试这个，这将解决您的问题。

select AddonName,count(AddonName) as countAdd,(select count(Version)from test1 as t where t.AddonName = test1.AddonName and t.Version = max(test1.Version)),(select count(Version) from test1 as t where t.AddonName = test1.AddonName and t.Version = min(test1.Version))from test1 GROUP BY AddonName; 

Answer 6

WITH cte 
AS 
(
    SELECT t.AddonName, MAX(t.Version) AS latest_version 
    FROM Table1 t 
    GROUP BY t.AddonName 
) 
SELECT t.AddonName, COUNT(t.WebsiteID) AS total_addons, 
SUM 
(
    CASE WHEN t.version = cte.latest_version 
    THEN 1 ELSE 0 END 
) AS up_to_date, 
SUM 
(
    CASE WHEN t.version <> cte.latest_version 
    THEN 1 ELSE 0 END 
) AS out_of_date 
FROM Table1 t 
JOIN cte ON t.AddonName = cte.AddonName 
GROUP BY t.AddonName