2021年12月21日 星期二

[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

















沒有留言:

張貼留言

[SQL]顯示千分位與小數顯示

  CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) CONVERT style參數說明 1  (expression為 money 或 smallmoney型別): 0 : 預設,保留小數位後兩位,並四捨...