我有一个表license_Usage,它的工作原理类似于一天中许可证使用情况的日志
ID User license date
1 1 A 22/2/2015
2 1 A 23/2/2015
3 1 B 22/2/2015
4 2 A 22/2/2015
我要计算每天每个用户有多少个许可证,结果应该像这样:
QuantityOfLicenses User date
2 1 22/2/2015
1 2 22/2/2015
为此,我做了以下查询:
select count(license) as [Quantity of licenses],[user],[date]
From license_Usage
where date = '22/2/2015'
Group by [date], [user]
哪个可行,但我想知道哪个用户使用了最多的许可证,为此,我执行了以下查询:
select MAX(result.[Quantity of licenses])
From (
select count(license) as [Quantity of licenses],[user],[date]
From license_Usage
Group by [date], [user]
) as result
它返回的最大值为2,但是当我想知道哪个用户使用了2个许可证时,我尝试执行以下查询未成功:
select result.user, MAX(result.[Quantity of licenses])
From (
select count(license) as [Quantity of licenses],[user],[date]
From license_Usage
Group by [date], [user]
) as result
Group by result.user
您可以使用如下形式:
select top 1 *
From (
select count(license) as Quantity,[user],[date]
From license_Usage
Group by [date], [user]
) as result
order by Quantity desc
如果需要提取所有具有max的行,以防万一,则必须使用rank()窗口函数
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句