根据条件返回某些记录(2) [英] Return certain record based on criteria (2)

问题描述

我之前曾问过这个问题，但是错过了我问题的重要部分.

I asked this question previously, but missed a vital part of my problem.

根据条件返回某些记录

获取此结果列表

Client  |   Date     | YESorNO
-------------------------------
A1      | 01/01/2001 | NO
A1      | 01/01/2002 | NO
A1      | 01/01/2003 | YES
A1      | 01/01/2004 | NO
A1      | 01/01/2005 | NO
A1      | 01/01/2006 | NO
A1      | 01/01/2007 | YES
A1      | 01/01/2008 | YES
A1      | 01/01/2009 | YES

A2      | 01/01/2001 | NO
A2      | 01/01/2002 | NO
A2      | 01/01/2003 | YES
A2      | 01/01/2004 | NO
A2      | 01/01/2005 | YES
A2      | 01/01/2006 | YES

A3      | 01/01/2001 | NO

           ...etc...

该列表按时间顺序排列，除降序/升序外，我无法通过其他任何方式对其进行排序.

The list is ordered chronologically and I cannot sort this is any other way other than descending / ascending.

我无法对是"进行排序|否，找到First()或Last()，因为这不会给我所需的值.

I cannot sort for Yes | NO and find the First() or Last() as this won't give me the required value.

每个客户都考虑完所有否"后，我希望能够返回第一个是".

在上述示例中，对于Client [A1]第7行是我要返回的记录(2007年1月1日).

In the above example for Client[A1] row 7 is the record I want returned (on 01/01/2007).

Client [A2]-第5行(2005年1月1日).. etc

Client[A2] - row 5 (01/01/2005) ..etc

我的代码如下

var query = 
(
    from m in db.MyTable
    where m.Criteria == XYZ
    select new
    {
      Client = m.Client,
      Date = m.Date, 
      YESorNO = m.YESorNO
    }
).OrderBy(x => x.Date);

使用.FirstOrDefault(x => x.YesOrNO == "YES")返回第三条记录.

@RenéVogt用户建议

User @RenéVogt advised that

var result = query.AsEnumerable()
                  .TakeWhile(x => x.YESorNO == "YES")
                  .LastOrDefault();

将完成并完成工作，但是我忘了补充一点，查询将返回许多客户端，并且我需要为每个客户端输入第一个是"，因此上述代码不足以满足要求.

would get the job done and it does, but I forgot to add that the query will be returning many Clients and I need the first 'YES' for each Client, therefore the above code won't suffice.

遍历我的结果将非常耗时，尽管这是一个解决方案，但我更希望此逻辑位于数据库查询本身中(如果可能)

Iterating over my results would be hugely time consuming and whilst that is a solution I would prefer this logic to be within the database query itself (if possible)

非常感谢

推荐答案

您要做的是按client分组，然后找到每个从结尾开始的最后一个YES .这样的事情(ClientList是List<>，您可能必须根据数据所在的位置进行更改):

What you have to do is grouping by client,and then find the last YES of each one starting from the end. Something like this (ClientList is a List<>, you may have to change it depending on where is your data):

var query = ClientList.OrderBy(x => x.client).ThenBy(x => x.date).GroupBy(x => x.client);
foreach (var client in query)
{
    var lastYES=client.Reverse().TakeWhile(x => x.YESorNO == "YES")
              .LastOrDefault();
    Console.WriteLine(String.Format("{0} {1}",client.Key,lastYES.date));
}
//Output: A1 01/01/2007 0:00:00
//        A2 01/01/2005 0:00:00

修改

Mansur Anorboev正确地建议按降序排序，从而消除了Reverse的需要，因此代码为:

Mansur Anorboev rightly suggested ordering by descending date, thus eliminating the need of Reverse, so the code would be:

var query = ClientList.OrderBy(x => x.client).ThenByDescending(x => x.date).GroupBy(x => x.client);
foreach (var client in query)
{
    var lastYES=client.TakeWhile(x => x.YESorNO == "YES")
              .LastOrDefault();
    Console.WriteLine(String.Format("{0} {1}",client.Key,lastYES.date));
}

编辑2

我仍然不完全满意我的解决方案，因为它正在使用foreach.这可以在一个Linq命令中完成所有操作:

I still was not completly happy with my solution, as it is using a foreach. This does everything in one Linq command:

var query = ClientList.OrderBy(x => x.client)
                      .ThenByDescending(x => x.date)
                      .GroupBy(x => x.client, (key, g) => g.TakeWhile(x => x.YESorNO == "YES").LastOrDefault())
                      .ToList();

这将返回一个列表，其中每个客户端具有一个元素并且具有正确的日期.

This returns a list with one element per client and with the correct date.

这篇关于根据条件返回某些记录(2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！