如何限制SQL查询最多返回某项? [英] How to limit an SQL query to return at most one of something?

问题描述

我的数据库中有一张表，代表软件的发行版；模式的相关列如下:

I have a table in my database representing releases of software; the relevant columns of the schema being as follows:

ProductID   int
PlatformID  tinyint
Date        datetime

全部都不为空，并且前两列还分配有唯一键.

All are not null, and there is also a unique key assigned to the first two columns.

我想构建一个查询(使用LINQ)，该查询返回每个平台(PlatformID)，每个平台(PlatformID)的最新记录(例如日期)，并且按日期降序排序.

I would like to build a query (using LINQ) which returns the latest release (e.g. Date) for each platform (PlatformID), but no more than one record per platform (PlatformID), and is sorted by Date descending.

我该怎么做?

这是我使用的解决方案:

Here is the solution I used:

var product = ...;

var latestReleases = product.Releases
    .GroupBy(p => p.PlatformID)
    .Select(group => group.OrderByDescending(p => p.Date).FirstOrDefault())
    .OrderByDescending(release => release.Date)

为清楚起见...

foreach (var release in latestReleases)
{
    Console.WriteLine("The latest release of MyProduct for {0} is {1}.",
        release.PlatformID, // of course this would be a string in real code ;)
        release.Version);
}

推荐答案

我认为以下代码可以满足您的要求:

I think the following code does what you’re asking for:

var latestProducts = db.Releases
    .GroupBy(p => p.PlatformID)
    .Select(grp => new {
        Platform = grp.Key,
        Latest = grp.OrderByDescending(p => p.Date).FirstOrDefault()
    })
    .OrderByDescending(entry => entry.Latest.Date);

foreach (var entry in latestProducts)
{
    Console.WriteLine("The latest product for platform {0} is {1}.",
        entry.Platform,
        entry.Latest.ProductID);
}

这是它的工作方式:

• 首先，拿走所有产品
• 通过通用PlatformID将它们分组.
• 在每个组中，按降序对该组中的产品进行排序，然后选择第一个(将是具有最新日期的产品)
• 为每个组创建一个包含PlatformID和平台最新产品的对象
• 按日期降序对对象列表进行排序.

这篇关于如何限制SQL查询最多返回某项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！