在内部联接中选择linq到sql
在内部联接中选择linq到sql
提问于 2013-11-05 10:09:33
我是linq中使用sql的初学者，我想知道内部联接中的select语法是什么：
inner join ( select CCL_TMA_ID as SecurityIdMax ,
max(CCL_DATE) as DateMax
from dbo.usrCOURSCLOTURE
where CCL_DONNEE is not null
and CCL_DATE <= @d
group by CCL_TMA_ID
)完整查询：
declare @d datetime
select @d = getdate()
select t0.CCL_TMA_ID as SecurityId ,
t0.CCL_DATE as Date ,
t0.CCL_DONNEE as Price ,
t1.CCL_DONNEE as CurrencyPrice
from dbo.usrCOURSCLOTURE as t0
inner join dbo.usrCOURSCLOTURE as t1 on t0.CCL_DEV_DONNEE = t1.CCL_TMA_ID
and t0.CCL_DATE = t1.CCL_DATE
and t1.CCL_DONNEE is not null
inner join ( select CCL_TMA_ID as SecurityIdMax ,
max(CCL_DATE) as DateMax
from dbo.usrCOURSCLOTURE
where CCL_DONNEE is not null
and CCL_DATE <= @d
group by CCL_TMA_ID
) cMax on t0.CCL_TMA_ID = SecurityIdMax
and t0.CCL_DATE <= DateMax
and t0.CCL_DATE >= DateMax-10
where t0.CCL_DATE > dateadd(year,-1,@d)回答 2
Stack Overflow用户
回答已采纳
发布于 2013-11-05 11:00:44
下面我为您做了一个查询，并给出了一些解释这些特性的注释。注意，您不能基于<=执行多个条件连接，例如
on t0.CCL_TMA_ID = SecurityIdMax
and t0.CCL_DATE <= DateMax
and t0.CCL_DATE >= DateMax-10您必须在第一个条件下加入，然后用where过滤掉它们。
例如
Datetime d = Datetime.Now;
Datetime lastYear = d.AddYears(-1);
var q = from t0 in db.usrCOURSCLOTURE
join t1 in db.usrCOURSCLOTURE.where(z => z.CCL_DONNEE.HasValue)
on new {a = t0.CCL_DEV_DONNEE, b = t0.CCL_DATE} equals new {a = t1.CCL_TMA_ID, b = t1.CCL_DATE}
// the above is how to do a join on multiple conditions
join t2 in (
from x0 in db.usrCOURSCLOTURE.where(z => z.CCL_DONNEE.HasValue && z.CCL_DATE < d)
.GroupBy(z => z.CCL_TMA_ID)
select new {SecurityIdMax = x0.Key, DateMax = x0.Max(z => z.CCL_DATE)}
//this is how you get your groupby subquery
)
on t0.CCL_TMA_ID equals t2.SecurityIdMax
where
t0.CCL_DATE > lastYear
&& t0.CCL_DATE <= t2.DateMax
&& t0.CCL_DATE >= SqlFunctions.DateAdd("DAY", -10, t2.DateMax) //nb not sure on the interval - correct this!
select new {SecurityId = t0.CCL_TMA_ID,
Date = t0.CCL_DATE,
Price = t0.CCL_DONNEE,
CurrencyPrice = t1.CCL_DONNEE};还请注意，"SqlFunctions“类位于System.Data.Entity程序集中的命名空间System.Data.Objects.SqlClient中。
Stack Overflow用户
发布于 2013-11-05 10:46:50
通过将语句封装在括号中，您将创建一个数据子集，在您的示例中，数据子集将从dbo.usrCOURSCLOTURE中按CCL_TMA_ID列分组。
为了使它更清晰，我们可以用不同的方式来表达：
@subsetOfData = select CCL_TMA_ID as SecurityIdMax, max(CCL_DATE) as DateMax
from dbo.usrCOURSCLOTURE
where CCL_DONNEE is not null and CCL_DATE <= @d
group by CCL_TMA_ID然后
select t0.CCL_TMA_ID as SecurityId ,
t0.CCL_DATE as Date ,
t0.CCL_DONNEE as Price ,
t1.CCL_DONNEE as CurrencyPrice
from dbo.usrCOURSCLOTURE as t0
inner join dbo.usrCOURSCLOTURE as t1 on t0.CCL_DEV_DONNEE = t1.CCL_TMA_ID
and t0.CCL_DATE = t1.CCL_DATE
and t1.CCL_DONNEE is not null
inner join @subsetOfData as cMax on t0.CCL_TMA_ID = SecurityIdMax
and t0.CCL_DATE <= DateMax
and t0.CCL_DATE >= DateMax-10
where t0.CCL_DATE > dateadd(year,-1,@d)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19786382
复制相关文章
相似问题
腾讯云开发者
