根据一列获取不同的行，并按日期排序 [英] Get Distinct rows according to one column and order by date

问题描述

我有以下情况可以在数据库级别或从Linq到EF级别解决:这是我在数据库中的视图:

I have the following scenario which can solved on the database level or the Linq to EF level: Here is my view in the database:

id  title   date           weight
==================================
1   t1     2013-01-18       1.5
1   t1     2013-01-17       1.4
1   t1     2013-01-15       1.31
1   t1     2013-01-12       1.22
2   t2     2013-01-19       2.3
2   t2     2013-01-16       2.1
2   t2     2013-01-07       1.81
2   t2     2013-01-19       1.62

因此，我需要的是每个项目(t1和t2)中的一条记录，这是按日期最新的一条记录.

What I need as a result is one record from each item (t1 and t2) which is the newest one by date.

所以输出将是这样的:

id  title   date           weight
==================================
1   t1     2013-01-18       1.5
2   t2     2013-01-19       2.3

就像我在上面说的那样，都欢迎使用(Distinct)在数据库级别或linq级别上回答.

As I said above Answers on the database level or the linq level using (Distinct) are both welcomed.

我的C#linq的一部分:

Part of my c# linq:

mylist = (from a in db.myview
join art in db.viewTags on a.id equals art.ArticleID
where (art.TagID == tag.ID)
select a).Distinct().Take(10).ToList();

我需要根据a.id(视图的id字段)从myview中获取不同的记录

I need distinct records from myview according to a.id (id field of the view)

谢谢

推荐答案

编辑-根据您要按ID区分的更新

EDIT - as per update you want distinct by id

全文全文: 在Linq中不同(通过财产)

Full article : DistinctBy in Linq (Find Distinct object by Property)

以下内容是 MoreLINQ 库的一部分.

Following is part of MoreLINQ library.

使用 DistinctBy 函数

public static IEnumerable<TSource> DistinctBy<TSource, TKey>
    (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
    HashSet<TKey> seenKeys = new HashSet<TKey>();
    foreach (TSource element in source)
    {
        if (seenKeys.Add(keySelector(element)))
        {
            yield return element;
        }
    }
}

因此，仅使用 Id 属性查找不同的值，您可以使用:

So to find the distinct values using just the Id property, you could use:

mylist = (from a in db.myview
 join art in db.viewTags on a.id equals art.ArticleID
 where (art.TagID == tag.ID)
 select a).DistinctBy(a=>a.Id).Take(10).ToList();

---

select * from table 
inner join
(select max(date) as date,id from table group by id) d 
on d.id = table.id and d.date= table.date

这篇关于根据一列获取不同的行，并按日期排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！