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

本文是小编为大家收集整理的关于根据标准返回某些记录 (2)的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我之前问过这个问题,但错过了我问题的一个重要部分.

根据条件返回特定记录

获取此结果列表

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...

该列表是按时间顺序排列的,除了降序/升序之外,我无法对其进行排序.

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

我希望能够在每个客户的所有"否"都被计算后返回第一个"是".

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

客户[A2] - 第 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 建议

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

会完成工作,但我忘了补充一点,查询将返回许多客户,我需要为每个客户提供第一个"是",因此上面的代码是不够的.

迭代我的结果将非常耗时,虽然这是一种解决方案,但我希望此逻辑位于数据库查询本身中(如果可能的话)

非常感谢

推荐答案

你要做的就是按client分组,然后找到每一个的最后一个YES 从末尾开始.像这样的东西(ClientList 是一个 List<>,你可能需要根据你的数据在哪里改变它):

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 的需要,因此代码为:

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 命令中完成所有操作:

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();

这将返回一个列表,其中每个客户一个元素和正确的日期.

本文地址:https://www.itbaoku.cn/post/1556904.html

问题描述

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

Return certain record based on criteria

Take this list of results

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.

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

I want to be able to return the first 'YES' after all 'NO's have been accounted for, per Client.

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

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

My code is as follows

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);

Using .FirstOrDefault(x => x.YesOrNO == "YES") returns the 3rd record.

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)

Many thanks

推荐答案

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

Edit

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));
}

Edit 2

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.