本文介绍了连接多个公用表表达式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个查询Query1:
I have two Query, Query1:
with cte as (
select
dbo.Cable.*,
row_number() over(partition by dbo.Cable.TagNo order by dbo.Cable.CableRevision desc) as rn
from dbo.Cable
where (dbo.Cable.CableRevision = @CoreRevision )
)
select *
from cte
where rn = 1
以及Query2
with cte as (
select
dbo.Cable.TagNo,dbo.Core.*,
row_number() over(partition by dbo.Core.CoreNo order by dbo.Core.CoreRevision desc) as rn
from dbo.Core INNER JOIN
dbo.Cable ON dbo.Cable.Id = dbo.Core.CableId
where (dbo.Core.CoreRevision <= @CoreRevision )
)
select *
from cte
where rn = 1
这两个查询与 Query1相关。 TagNo
和 Query2.TagNo
我如何使用这两个查询,是否有可能用 With
命令来执行吗?
these two query are related by Query1.TagNo
and Query2.TagNo
how can i use join these two querys, is it possible to do that with With
Command?
谢谢
推荐答案
尝试此查询,也许这就是您要寻找的内容。
Try this query, perhaps this is what you are looking for.
;WITH cte AS
(SELECT dbo.Cable.*,
row_number() over(partition by dbo.Cable.TagNo order by dbo.Cable.CableRevision desc) as rn
FROM dbo.Cable
WHERE dbo.Cable.CableRevision = @CoreRevision
), cte2 AS
(SELECT dbo.Cable.TagNo, dbo.Core.*,
row_number() over(partition by dbo.Core.CoreNo order by dbo.Core.CoreRevision desc) as rn
FROM dbo.Core INNER JOIN dbo.Cable ON dbo.Cable.Id = dbo.Core.CableId
WHERE dbo.Core.CoreRevision <= @CoreRevision
)
SELECT *
FROM cte c FULL JOIN cte2 c2 ON c.TagNo = c2.TagNo
WHERE c.rn = 1 OR c2.rn = 1
这篇关于连接多个公用表表达式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!