SQL 查询的 From 子句中的 CTE [英] CTE in From clause of SQL Query

问题描述

我需要在 SQL 查询的 from 子句中使用 CTE 查询

I need to use CTE query inside from clause of SQL Query

看这个例子:

Drop Table #Temp
Drop Table #Temp2
Create Table #Temp(name1 text, name2 text)

Insert INTO #Temp Values ('test','test')
Insert INTO #Temp Values ('test','test')

select * into #Temp2
from #Temp

Select * from #Temp2

在这里，我只是通过从 Temp 中选择记录将行插入到临时表Temp2"中……这工作正常……

Here, I am just inserting rows into temp table 'Temp2' from selecting records from Temp... this is working fine...

但我的需要是，必须在 from 子句中使用 CTE.. like

But my need is, have to use CTE inside from clause.. like

select * into #Temp2
from (;With CTE as ( Select * from #Temp) select * from CTE)

请不要鼓励我分开 CTE 查询..因为，我无法控制那部分查询，因为它是由其他系统提供的.

Please don't encourage me to separate CTE query..because, I can't control that part of query since it is being provided by other system.

select * into #Temp2
from ("Query Provided by Other System")

所以由其他系统提供的查询"可能是也可能不是 CTE 查询.

So the "Query Provided by Other System" may or may not be the CTE query.

推荐答案

use below query



  Create Table #Temp(name1 text, name2 text)

    Insert INTO #Temp Values ('test','test')
    Insert INTO #Temp Values ('test','test')
    GO

    With CTE as ( Select * from #Temp) 
    select * into #Temp2 from CTE

    select * from #Temp2
    GO
    Drop Table #Temp
    Drop Table #Temp2

这篇关于SQL 查询的 From 子句中的 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！