[SQL]CTE (Common Table Expression)

 CTE 是一個「暫存」且「具名」的結果集合。
CTE 會暫時儲存 AS 括號中的 Query 結果，
用在同一個執行中 SELECT 、 INSERT 、 UPDATE 、 DELETE，
或是 CREATE VIEW 的 SELECT 上。
也可以在定義 CTE 的 Query 中使用，今天介紹的遞迴就是一個例子。

以下為範例--

DECLARE @iLottery2_Running_Period int = 0

DECLARE @iLottery2_BuffTime_Period int = 0

DECLARE @tLottery2_Period_EndTime datetime;

--------------------         原來語法       -----------------------------

SELECT TOP 1 @iLottery2_Running_Period=ISNULL(Period, 0)

FROM   GroupLotteryPeriod

WHERE  GroupLotteryID = 1 AND GroupLotteryType = 2

   AND  PeriodStatus = 1

SELECT TOP 1 @iLottery2_BuffTime_Period=ISNULL(Period, 0)

FROM   GroupLotteryPeriod

WHERE  GroupLotteryID = 1 AND GroupLotteryType = 2

   AND  PeriodStatus = 2

SELECT TOP 1 @tLottery2_Period_EndTime=ISNULL(EndDateTime, 0)

FROM   GroupLotteryPeriod

WHERE  GroupLotteryID = 1 AND GroupLotteryType = 2

   AND  Period = @iLottery2_Running_Period

*需要同一張表格select 3次

---------------------------------使用CTE 撰寫---------------------------------

WITH LotteryPeriodStatus(Status)

AS (SELECT 1

UNION ALL

SELECT 2),

GroupLotteryPeriodTemp

AS (SELECT Status,GLP.Period,GLP.EndDateTime

FROM   LotteryPeriodStatus LPS

LEFT JOIN dbo.GroupLotteryPeriod GLP

   ON LPS.Status = GLP.PeriodStatus

  AND GLP.GroupLotteryID = 1

  AND GLP.GroupLotteryType = 2)

SELECT @iLottery2_Running_Period=ISNULL(GLPT1.Period, 0),

   @iLottery2_BuffTime_Period=ISNULL(GLPT2.Period, 0),

   @tLottery2_Period_EndTime =ISNULL(GLPT1.EndDateTime, '1900-01-01')

FROM   GroupLotteryPeriodTemp GLPT1

   CROSS JOIN GroupLotteryPeriodTemp GLPT2

WHERE  GLPT1.status = 1  -- 1:Running Period

   and GLPT2.status = 2  -- 2:Buffer Period

*一次CTE就可以解決

SELECT @iLottery2_Running_Period,@iLottery2_BuffTime_Period,@tLottery2_Period_EndTime