获取列和id的最大值

Question

我在一个表中有以下列

MovieID - Point - 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 and Limit简单地做到这一点。您通过MovieID和Limit to 1进行订购，将为您提供得分最多的电影信息。不要忘记根据你的偏好和你的刻度将其改为升序或降序。

Select MovieID, Point Ordery By MovieID Limit 1

Answer 2

你的内部查询应该足够了，只要在平均值之后调用Max()。

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).Max()
}