sql - 获取列的最大值和id

Question

我在表中有以下列

MovieID - 点 - CategoryID

1-5-1

1-6-2

1-4-3

2-7-1
...

如您所见，每部电影都有多个类别。我想找到最高分的电影。但是这个点必须是所有类别的平均值。以下 linq 查询可以找到我想要的最大点，但我也需要 movieID。

from tmp in
(from tmp in (
    (from Rating in db.Rating
    where
      Rating.DistrictID == 1
    group Rating by new {
      Rating.MovieID
    } into g
    select new {
      g.Key.MovieID,
      avg_point = (double?)g.Average(p => p.Point)
    }))
select new {
  tmp.avg_point,
  Dummy = "x"
})
group tmp by new { tmp.Dummy } into g
select new {
  max_point = (double?)g.Max(p => p.avg_point)
}

- - - - - - - - - - - - - - - -回答
感谢@Sari Rahal。我想出了以下 linq 查询:

(from tmp in (
    (from Rating in db.Rating
    where
      Rating.DistrictID == 1
    group Rating by new {
      Rating.MovieID
    } into g
    select new {
      g.Key.MovieID,
      avg_point = (double?)g.Average(p => p.Point)
    }))
orderby
  tmp.avg_point descending
select new {
  tmp.MovieID,
  tmp.avg_point
}).Take(1)

Answer 1

您可以简单地通过 Order By 和 Limit 做到这一点。您按 MovieID 排序，Limit to 1 将为您提供得分最高的电影信息。不要忘记根据您的偏好和您的比例尺的工作方式将其更改为升序或降序。

Select MovieID, Point Ordery By MovieID Limit 1