mysql - 当我从表中获取最大列时必须获取相应的时间戳

Question

我需要从表中提取所需字段以及相关时间戳

SELECT * FROM Glm_Test.LicenseUsage where FeatureId='2';
Output :
VendorId,FeatureId,Total_Lic_Installed,Total_Lic_Used,Reserved,CurrentTime
1   2   106 19  67  2015-12-15 15:00:05
1   2   106 19  67  2015-12-15 15:02:02
1   2   106 19  69  2015-12-15 15:04:02
1   2   106 19  67  2015-12-15 15:06:01
1   2   106 20  67  2015-12-15 15:08:02
select VendorId,FeatureId,Total_Lic_Installed,Max(Total_Lic_Used),Reserved,CurrentTime from Glm_Test.LicenseUsage where FeatureId= '2' group by VendorId,FeatureId;
output:
1   2   106 20  69  2015-12-15 15:00:05

在上面的2个查询中第一个查询列出表中的所有条目

我希望第二个查询返回 Total_Lic_Used 列的最大值的时间戳，但不知何故它只返回第一个条目的时间戳。

非常感谢您的帮助。

Answer 1

选择不属于聚合函数(如 count/max/min/sum...)或不在 group by 子句中的列将给出 意外的结果:

其他 RBBMS 不会允许这些语句(给出类似错误):

sql server ==> the select list because it is not contained in either an aggregate function or the GROUP BY clause

Oracle ==>not a GROUP BY expression

您可以通过子查询和加入

来完成此操作

select 
    a.VendorId,
    a.FeatureId,
    a.Total_Lic_Installed,
    b.max_Total_Lic_Used,
    a.Reserved,
    a.CurrentTime 
from Glm_Test.LicenseUsage a 
join (
    select 
        VendorId,
        FeatureId,
        Max(Total_Lic_Used) max_Total_Lic_Used
    from Glm_Test.LicenseUsage 
    where FeatureId = '2' 
    group by VendorId, FeatureId
) b
on a.VendorId = b.VendorId and 
a.FeatureId = b.FeatureId and
a.Total_Lic_Used = b.max_Total_Lic_Used

sql fiddle demo

你也可以尝试这个；

select 
    `VendorId`, 
    `FeatureId`, 
    `Total_Lic_Installed`, 
    `Total_Lic_Used`, 
    `Reserved`, 
    `CurrentTime`
from Glm_Test.LicenseUsage
order by Total_Lic_Used desc
limit 1

demo