sqlserver with...as用法语法实例讲解

SQL SERVER CTE

语法

[ WITH  [ ,n ] ] 
::= 
        expression_name [ ( column_name [ ,n ] ) ] 
    AS 
        ( CTE_query_definition ) 

栗子1：

WITH tb_yield AS (
SELECT  SUM(wdy.QTY) qty  ,cl.LINE, wdy.WEEK  FROM WIP_DAILY_YIELD wdy JOIN CFG_LINE cl
    ON wdy.LINE_GUID = cl.GUID
  WHERE SUBSTRING(wdy.WEEK,1,4) ='2018'
   GROUP BY cl.LINE, wdy.WEEK     
)
SELECT SUM(num) CNT, LINE,sum(qty) QTY FROM (
    SELECT qty,LINE ,week,CONVERT(int,(ROW_NUMBER() OVER(PARTITION BY WEEK ORDER by qty desc))) as num FROM tb_yield
) AS A
  WHERE  A.num =1
  GROUP BY A.LINE

栗子2：

with cte  
as 
(  
select 1 as col1,'aa' as col2  
union all 
select 2,'bb' 
)  

--把cte的数据存储在tb_cte表  
select * into newtable  from cte  

select * from newtable  ;  

--运用cte，删除数据  
;with cte_delete  
as 
(  
select * from newtable  
)  

delete from cte_delete where col1 = 1