欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-23 11:57:27
SQL SERVER CTE 语法 [ WITH [ ,n ] ] ::= expression_name [ ( column_name [ ,...

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