代码之家 › 专栏 › 技术社区 › Paul Rowland

将带有按子句分组和/或聚合函数的Sql查询转换为linq到Sql?

group-by tsql linq-to-sql

Paul Rowland · 技术社区 · 16 年前

SELECT * FROM AuditTable adt1
    inner join UserTable usr
        on adt1.[UserName] = usr.[User_Id]
WHERE [TimeStamp] = ( SELECT MAX([TimeStamp])
                      FROM AuditTable adt2
                      WHERE adt1.UserName = adt2.UserName)

用sql编写查询的另一种方法是:;

SELECT adt1.[UserName], usr.First_Name, max([TimeStamp]) as [TimeStamp]
FROM AuditTable adt1
    INNER JOIN UserTable usr
        on adt1.[UserName] = usr.[User_Id]
GROUP BY adt1.[UserName] ,usr.First_Name

2 回复 | 直到 16 年前

KristoferA 16 年前

按adt1分组adt1。用户名int ag

Paul Rowland 16 年前

我让它工作。..答案是使用let关键字。

作为旁白,我强烈建议使用 LinqPad 当试图解决这些问题时。

from adt in AuditTable
    join usr in UserTable
        on adt.UserName equals usr.User_Id
group adt by 
    new { adt.UserName, adt.Roles, usr.First_Name, usr.Last_Name } 
into g
let LastAccessed = g.Max(a => a.TimeStamp)
select new 
    { UserName = g.Key.UserName,
      Roles = g.Key.Roles,
      FirstName = g.Key.First_Name,
      LastName = g.Key.Last_Name,
      TimeStamp = LastAccessed
    }

推荐文章