用于从列中获取最大值的 SQL 查询 [英] SQL Query for getting maximum value from a column

问题描述

我在数据库中获得了有关用户在特定活动上花费时间的数据.

I have got this data in the database about users spending time on a particular activity.

我打算在每个用户花费最大小时数时获取数据.

I intend to get the data when every user has spent the maximum number of hours.

像这样:

Select Id, Name, HoursSpent, Date from HoursSpent
    Where HoursSpent = (SELECT MAX(HoursSpent) FROM HoursSpent)

但它只为我提供最长小时数的重复数据行，我也希望获得没有重复数据(如 Person2 和 Person9)的用户.

But it is only giving me rows for duplicate data with maximum hours, I would like to get the users as well who don't have duplicate data like Person2 and Person9 as well.

推荐答案

你想要一个 correlation 子句:

select hs.Id, hs.Name, hs.HoursSpent, hs.Date
from HoursSpent hs
where hs.HoursSpent = (select max(hs2.HoursSpent) 
                       from HoursSpent hs2
                       where hs2.name = hs.name
-----------------------------^ this is the correlation clause
                      );

这表示对于 HoursSpent 中的每个 name，选择具有最大值 HoursSpent 的行.

This says that for each name in HoursSpent, choose the row that has the maximum value of HoursSpent.

这篇关于用于从列中获取最大值的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！