将group by/min日期从sql转换为linq

ycl3bljg 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(301)

我在将sql转换为linq时遇到了问题，为了处理预期的结果。。。
考虑下表：

pkIdentifier  fkProject    fkFirst     fkSecond    Date
1             1030         1           1           03.08.2020 10:05:00
2             1030         1           2           03.08.2020 10:11:00
3             1030         3           1           03.08.2020 14:00:00
4             1030         3           2           03.08.2020 14:02:00
5             1040         1           1           03.08.2020 15:00:00
6             1050         2           1           04.08.2020 08:00:00
7             1050         2           2           04.08.2020 08:10:00
8             1050         2           3           04.08.2020 08:15:00

我在这里需要的是找到项目，与最大的“秒”-如果有更多的条目，然后与最年轻的日期之一。
也就是说，上表的结果是：

pkIdentifier  fkProject    fkFirst     fkSecond    Date
2             1030         1           2           03.08.2020 10:11:00
5             1040         1           1           03.08.2020 15:00:00
8             1050         2           3           04.08.2020 08:15:00

使用下面的sql，结果与我预期的几乎相同：

SELECT ps.fkProject, ps.fkFirst, ps.fkSecond, min(ps.Date) as Date
FROM ProjectsStatus ps
    INNER JOIN (
        SELECT a.fkProject, MAX(a.fkSecond) as maxSecond
        FROM ProjectStatus a
        GROUP BY a.fkProject
    ) b ON ps.fkProject = b.fkProject and ps.fkSecond = b.maxSecond
group by ps.fkProject, ps.First, ps.Second

我在linq上的尝试，但是如果有更多具有相同“fksecond”的条目，则仅缺少选择具有最年轻日期的条目：

var query = this.dataContext.ProjectStatus.Where(
            x => x.fkSecond == this.dataContext.ProjectStatus.Where(y => y.fkProject == x.fkProject).Max(s => s.fkSecond)
          );

我现在也得到了项目1030的第二行fksecond=2，但这不是我想要的。我只想要最年轻的那个，是2号。

pkIdentifier  fkProject    fkFirst     fkSecond    Date
2             1030         1           2           03.08.2020 10:11:00
4             1030         3           2           03.08.2020 14:02:00
5             1040         1           1           03.08.2020 15:00:00
8             1050         2           3           04.08.2020 08:15:00

sql c#linq

来源：https://stackoverflow.com/questions/63228992/translating-group-by-min-date-from-sql-to-linq

2条答案

按热度按时间

anhgbhbe1#

请参见以下内容：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication163
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("pkIdentifier", typeof(int));
            dt.Columns.Add("fkProject", typeof(int));
            dt.Columns.Add("fkFirst", typeof(int));
            dt.Columns.Add("fkSecond", typeof(int));
            dt.Columns.Add("Date", typeof(DateTime));

            dt.Rows.Add(new object[] { 1, 1030, 1, 1, new DateTime(2020, 3, 8, 10, 5, 0)});
            dt.Rows.Add(new object[] { 2, 1030, 1, 2, new DateTime(2020, 3, 8, 10, 11, 0)});
            dt.Rows.Add(new object[] { 3, 1030, 3, 1, new DateTime(2020, 3, 8, 14, 0, 0)});
            dt.Rows.Add(new object[] { 4, 1030, 3, 2, new DateTime(2020, 3, 8, 14, 2, 0)});
            dt.Rows.Add(new object[] { 5, 1040, 1, 1, new DateTime(2020, 3, 8, 15, 0, 0)});
            dt.Rows.Add(new object[] { 6, 1050, 2, 1, new DateTime(2020, 3, 8, 8, 0, 0)});
            dt.Rows.Add(new object[] { 7, 1050, 2, 2, new DateTime(2020, 3, 8, 8, 10, 0)});
            dt.Rows.Add(new object[] { 8, 1050, 2, 3, new DateTime(2020, 3, 8, 8, 15, 0)});

            DataTable dt2 = dt.AsEnumerable()
                .OrderBy(x=> x.Field<int>("fkProject"))
                .ThenByDescending (x => x.Field<int>("fkSecond"))
                .ThenBy(x => x.Field<DateTime>("Date"))
                .GroupBy(x => x.Field<int>("fkProject"))
                .Select(x => x.First())
                .CopyToDataTable();

        }
    }
}

赞(0）回复(0）举报 2021-07-24

nnsrf1az2#

您可以使用groupby，如下例所示：

class Data
{
    public int pkIdentifier { get; set; }
    public int fkProject { get; set; }
    public int fkFirst { get; set; }
    public int fkSecond { get; set; }
    public DateTime date { get; set; }
}

List<Data> dataList = new List<Data>();

dataList.Add(new Data { pkIdentifier = 1, fkProject = 1030, fkFirst = 1, fkSecond = 1, date = new DateTime(2020, 08, 03, 10, 05, 00) });
dataList.Add(new Data { pkIdentifier = 2, fkProject = 1030, fkFirst = 1, fkSecond = 2, date = new DateTime(2020, 08, 03, 10, 11, 00) });
dataList.Add(new Data { pkIdentifier = 3, fkProject = 1030, fkFirst = 3, fkSecond = 1, date = new DateTime(2020, 08, 03, 14, 00, 00) });
dataList.Add(new Data { pkIdentifier = 4, fkProject = 1030, fkFirst = 3, fkSecond = 2, date = new DateTime(2020, 08, 03, 14, 02, 00) });
dataList.Add(new Data { pkIdentifier = 5, fkProject = 1040, fkFirst = 1, fkSecond = 1, date = new DateTime(2020, 08, 03, 15, 00, 00) });
dataList.Add(new Data { pkIdentifier = 6, fkProject = 1050, fkFirst = 2, fkSecond = 1, date = new DateTime(2020, 08, 04, 08, 00, 00) });
dataList.Add(new Data { pkIdentifier = 7, fkProject = 1050, fkFirst = 2, fkSecond = 2, date = new DateTime(2020, 08, 04, 08, 10, 00) });
dataList.Add(new Data { pkIdentifier = 8, fkProject = 1050, fkFirst = 2, fkSecond = 3, date = new DateTime(2020, 08, 04, 08, 15, 00) });

var groupProject = dataList.GroupBy(i => i.fkProject).Select(groupItem => groupItem.OrderByDescending(dataItem => dataItem.fkSecond).ThenBy(dataItem => dataItem.date).First());

groupby将按项目编号分组，然后按秒排序，然后按日期降序排列。然后取每组的第一个元素。

赞(0）回复(0）举报 2021-07-24

我来回答

将group by/min日期从sql转换为linq

2条答案

相关问题

热门标签

最新问答