[c#]复杂的 SQL JOIN 查询,分钟,马克斯和日期范围

发布时间: 2017/2/28 20:55:20
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有下面的表︰

Readings:
+----+---------------------+-------+----------+
| Id |   TimestampLocal    | Value | Meter_Id |
+----+---------------------+-------+----------+
|  1 | 2014-08-22 18:05:03 | 50.5  |        1 |
|  2 | 2013-08-12 14:02:09 | 30.2  |        1 |
+----+---------------------+-------+----------+

Meters:
+----+--------+
| Id | Number |
+----+--------+
|  1 |  32223 |
+----+--------+

我需要选择 2 个读数为每米、 与最大日期时间阅读和阅读与 min DateTime,除了两个读数,就像这样的值之间的差异︰

+----------+------------+----------------+------------+----------------+------------+
| Meter_Id | MaxReading | MaxReadingTime | MinReading | MinReadingTime | Difference |
+----------+------------+----------------+------------+----------------+------------+

我需要一个单一的查询来实现这一目标在实体框架中的日期范围内的所有米

我是能够得到这个远 (max 和 min 测得)︰

SELECT 
    tt.*
FROM Readings tt
INNER JOIN
    (
    SELECT 
        Meter_Id, 
        MAX(TimeStampLocal) AS MaxDateTime, 
        MIN(TimeStampLocal) AS MinDateTime
    FROM Readings 
    where TimeStampLocal > '2014-12-08'
    GROUP BY Meter_Id
    ) AS groupedtt 
ON (tt.Meter_Id = groupedtt.Meter_Id)   AND 
    (tt.TimeStampLocal = groupedtt.MaxDateTime or tt.TimeStampLocal = groupedtt.MinDateTime) 
order by Meter_Id;

解决方法 1:

使用此样机的你实际的架构和数据︰

class Reading
{
    public int Id { get; set; }
    public DateTime TimestampLocal { get; set; }
    public double Value { get; set; }
    public int Meter_Id { get; set; }
}

List<Reading> Readings = new List<Reading>()
{
    new Reading { Id = 1, TimestampLocal = new DateTime(2014, 8, 22), Value = 50.5, Meter_Id = 1 },
    new Reading { Id = 2, TimestampLocal = new DateTime(2013, 8, 12), Value = 30.2, Meter_Id = 1 },
    new Reading { Id = 3, TimestampLocal = new DateTime(2013, 9, 12), Value = 35.2, Meter_Id = 1 }
};

使用这种 linq 查询︰

        var q = from r in Readings
                group r by r.Meter_Id into rGroup
                select new
                {
                    Meter_Id = rGroup.Key,
                    MaxReading = rGroup.OrderByDescending(x => x.TimestampLocal).First().Id,
                    MaxReadingTime = rGroup.OrderByDescending(x => x.TimestampLocal).First().TimestampLocal,
                    MinReading = rGroup.OrderBy(x => x.TimestampLocal).First().Id,
                    MinReadingTime = rGroup.OrderBy(x => x.TimestampLocal).First().TimestampLocal,
                    Difference = rGroup.OrderByDescending(x => x.TimestampLocal).First().Value -
                                 rGroup.OrderBy(x => x.TimestampLocal).First().Value
                };

产生的输出为︰

[0] = { Meter_Id = 1, MaxReading = 1, MaxReadingTime = {22/8/2014 12:00:00 πμ}, 
        MinReading = 2, MinReadingTime = {12/8/2013 12:00:00 πμ}, Difference = 20.3 }

其中应接近预期的结果。

编辑︰

你可以大幅简化上述 linq 查询通过使用 let 子句︰

var q = from r in Readings
        group r by r.Meter_Id into rGroup
        let MaxReading = rGroup.OrderByDescending(x => x.TimestampLocal).First()
        let MinReading = rGroup.OrderBy(x => x.TimestampLocal).First()
        select new
        {
            Meter_Id = rGroup.Key,
            MaxReading = MaxReading.Id,
            MaxReadingTime = MaxReading.TimestampLocal,
            MinReading = MinReading.Id,
            MinReadingTime = MinReading.TimestampLocal,
            Difference = MaxReading.Value - MinReading.Value
        };
官方微信
官方QQ群
31647020