SQL SERVER CTE
语法
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
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