SQL中join group by having max() 时转Linq

本来开发时有一个分组聚合的脚本，比较复杂，为了笔记效果，所以将脚本做一个简化。

本来库里有两个表TableA和TableB，两个表的主键做如下关联：

--TableA的主键ID为TableB的外键Aid
SELECT a.Id, a.Name, b.Id, b.Name, b.Aid, b.CreateTime
FROM TableA a
JOIN TableB b ON a.Id=b.Aid

现在需要根据TableA的Aid分组，取出每个Aid对应TableB的最新CreateTime的一个的Name数据，脚本如下：

SQL写法一：

SELECT 
a.Id, 
b.Name
FROM TableA a
JOIN TableB b ON b.AId=a.Id AND EXISTS (SELECT 1 FROM TableB d WHERE d.AId=b.AId GROUP BY d.AId HAVING b.CreateTime=MAX(d.CreateTime))

根据上面的SQL可见，在join的条件中，我使用了Having作为聚合后的判断条件，这个脚本也可以变换为下面的写法：

SQL写法二：

SELECT 
a.Id, 
b.Name
FROM TableA a
JOIN TableB b ON b.AId=a.Id AND EXISTS (SELECT 1 FROM (SELECT d.AId, MAX(d.CreateTime) AS MaxCreateTime FROM TableB d WHERE d.AId=b.AId GROUP BY d.AId) AS d1 WHERE b.CreateTime=d1.MaxCreateTime)

上面的变换写法，可不是为了花哨展示，而是因为Linq本身不能直接转换SQL写法一，而可以转换为SQL写法二。

那么SQL写法二也为SQL转为Linq提供了一个思路，就是先将最大的CreateTime取出来，然后再作为条件进行查询，那么最终的Linq写法就出来了：

// 先将CreateTime的Max作为条件写好
var wherequery = from a in TableB
                 where (from b in TableB where b.AId == a.AId group b by b.AId into b1 select b1.Max(c => c.CreateTime)).Contains(a.CreateTime)
                 select a;

// 将上面的linq再加入下面的linq中作为条件：
var aa = from a in TableA
         join b in wherequery on a.Id equals b.AId
         select new
         {
             AId = a.Id,
             b.Name
         };

至此，一个复杂的group by having max()查询就完成了。

如果对你有帮助，点赞支持一下，谢谢！