使用LINQ模拟SQL Server 2012滞后窗口功能 [英] Emulating SQL Server 2012 Lag Window function with LINQ
本文介绍了使用LINQ模拟SQL Server 2012滞后窗口功能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一些示例数据,如下所示:(我没有SQL Server 2012)
I've got some sample data that look like: ( I don't have SQL Server 2012)
create table #base
(pat_id int
,admission_date date
,discharge_date date
)
go
insert into #base
values
(1, '2007-01-04', '2007-01-04'),
(1, '2007-01-10', '2007-01-10'),
(1, '2007-01-11', '2007-01-11'),
(1, '2007-01-18', '2007-01-18'),
(1, '2007-01-24', '2007-01-24'),
(1, '2008-01-25', '2008-01-26'),
(2, '2007-02-01', '2007-02-01'),
(2, '2007-02-06', '2007-02-06'),
(2, '2007-02-07', '2007-02-07'),
(2, '2007-02-08', '2007-02-08')
这是我想用LINQ模拟的SQL查询
Here's the SQL query I wish to emulate with LINQ
;with cte
as
(
select pat_id
,admission_date
,discharge_date
,ROW_NUMBER() over(partition by pat_id order by admission_date asc) as rn
from #base
)
select firstCte.pat_id
,firstCte.discharge_date as firstAdmitReference
,secondCte.admission_date as dischargeReference
,case when DATEDIFF(DAY,firstCte.discharge_date,secondCte.admission_date) <= 30 then 1 else 0 end Readmit
from cte as firstCte
inner join cte as secondCte
on firstCte.pat_id = secondCte.pat_id
where firstCte.rn = secondCte.rn -1
以下是该查询的结果:
create table #readmit_data
( pat_id int
,admission_date date
,discharge_date date
,within_x_days int
)
insert into #readmit_data(pat_id,admission_date,discharge_date,within_x_days)
values
(1, '2007-01-04', '2007-01-10', 1),
(1, '2007-01-10', '2007-01-11', 1),
(1, '2007-01-11', '2007-01-18', 1),
(1, '2007-01-18', '2007-01-24', 1),
(1, '2007-01-24', '2008-01-25', 0),
(2, '2007-02-01', '2007-02-06', 1),
(2, '2007-02-06', '2007-02-07', 1),
(2, '2007-02-07', '2007-02-08', 1)
在此结果集中,数据的基本格式为
In this result set the basic format for the data is
patient ID dischargeDate nextVisitAdmitDate
within_x_days
列中的
1表示患者在上一次出院30天后已再次入院.理想情况下,30
将是用户输入的变量.
A 1 in the within_x_days
column indicates that a patient had a readmission with 30 days of their last discharge. Ideally, 30
will be a variable entered in by the user.
LINQ中可以使用哪种构造?
What sort of construct in LINQ is there available for doing this?
推荐答案
到LINQ的窗口是.NET扩展功能库
Window To LINQ is a library of extension functions for .NET
这篇关于使用LINQ模拟SQL Server 2012滞后窗口功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文