EF Core中Partition by实现
一、SQL语句实现
Partition by是SQL Server数据库中提供的分区函数,跟Group by不同的是,Partition by能够按照分区返回所有记录,而Group by只能返回一条记录。
举个例子,有如下的数据库,需要找出每个唯一编号最新状态的数据。
显然,CW048201和CW048202它们的最新状态都是取消报废状态,用Group By去获取单行数据是无法获取的。这里我就可以借助于Partition By。
SQL语句如下:
select * from ( select ROW_NUMBER() over (partition by EPC order by Scrapdate desc) as fid,* from [dbo].[hps_BaoFei] ) a
语句执行结果如下:
可以看出分区函数Partitiion By按照编码分为了两个部分并且每个部分按照时间降序排列,因此我们只需再加一个条件即可获取每个编码的最新数据。
SQL语句如下:
select * from ( select ROW_NUMBER() over (partition by EPC order by Scrapdate desc) as fid,* from [dbo].[hps_BaoFei] ) a where a.fid = 1
语句执行结果如下:
二、EF Core的实现
直接上代码,我将其封装到了Web API里面,可以直接返回结果。
[HttpGet] [Route("/api/GetBaofei")] public List<hps_BaoFei> GetBaofei() { using (BroadswordContext _bc = new BroadswordContext()) { List<hps_BaoFei> list = (from p in _bc.hps_BaoFeis.Select(t => t.EPC).Distinct() from q in _bc.hps_BaoFeis .Where(t => t.EPC == p) .OrderByDescending(t => t.Scrapdate) .Take(1) select q).ToList(); return list; } }
注意其中的核心代码部分,我们可以Debug看一下其生成的SQL语句。
SELECT [t0].[id], [t0].[EPC], [t0].[Remarks], [t0].[ScrapRole], [t0].[ScrapState], [t0].[Scrapdate], [t0].[Scraptime] FROM ( SELECT DISTINCT [h].[EPC] FROM [hps_BaoFei] AS [h] ) AS [t] INNER JOIN ( SELECT [t1].[id], [t1].[EPC], [t1].[Remarks], [t1].[ScrapRole], [t1].[ScrapState], [t1].[Scrapdate], [t1].[Scraptime] FROM ( SELECT [h0].[id], [h0].[EPC], [h0].[Remarks], [h0].[ScrapRole], [h0].[ScrapState], [h0].[Scrapdate], [h0].[Scraptime], ROW_NUMBER() OVER(PARTITION BY [h0].[EPC] ORDER BY [h0].[Scrapdate] DESC) AS [row] FROM [hps_BaoFei] AS [h0] ) AS [t1] WHERE [t1].[row] <= 1 ) AS [t0] ON [t].[EPC] = [t0].[EPC]
执行结果: