遞迴/樹狀查詢.
1.日期區間產生
2.員工階層表
3.依特定字串拆解文字(unpivot)
日期區間產生
測試SQL:
;WITH CTE (Dates,eDate) AS
(
SELECT sDate AS Dates,eDate AS eDate
FROM (select cast('2018/10/01' as datetime) as sDate,cast('2018/12/01' as datetime) as eDate) a
UNION ALL
SELECT DATEADD(DAY,10,Dates),eDate
FROM CTE
WHERE DATEADD(DAY,10,Dates) < eDate
)
SELECT CTE.Dates
FROM CTE
員工階層表
Step1:定義錨點(第一層)產生的資料集合
以boss為開始錨點(ParentId is null),設定JobLevel=1
Step2:定義判斷條件(第二~N層)產生的資料集合
條件:ON A.ParentId = B.EmpID;找出各員工的父階,若父階為1,員工就是1+1階,若父階為2,員工就是2+1階。
DECLARE @myEmp TABLE (
[EmpID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ParentId] [int],
[Path] nvarchar(50)
);
INSERT INTO @myEmp values ('1','boss',null,'/')
,('2','managerA','1','/1/')
,('3','managerB','1','/2/')
,('4','managerC','1','/3/')
,('5','empA','2','/1/1/')
,('6','empB','2','/1/2/')
,('7','empC','3','/2/3/')
,('8','empD','6','/1/1/2/')
,('9','empE','6','/1/1/3/');
WITH EmpHierarchy AS (
--Step1:定義錨點(第一層)產生的資料集合
--以boss為開始錨點(ParentId is null),設定JobLevel=1
SELECT EmpID, Name
, Path
, ParentId
, 1 AS JobLevel
FROM @myEmp
WHERE ParentId is null
UNION ALL
--Step2:定義判斷條件(第二~N層)產生的資料集合
--條件:ON A.ParentId = B.EmpID;找出各員工的父階,若父階為1,員工就是1+1階,若父階為2,員工就是2+1階。
SELECT A.EmpID, A.Name
, A.Path, A.ParentId
, (B.JobLevel + 1) AS JobLevel
FROM @myEmp A
INNER JOIN EmpHierarchy B ON A.ParentId = B.EmpID
)
SELECT * FROM EmpHierarchy
依特定字串拆解文字(unpivot)
--Step1:定義錨點(第一層)產生的資料集合
--以此例,錨點為每筆資料到第一次出現@splitter的位置A
--Step2:定義判斷條件(第二~N層)產生的資料集合
--loop:從位置A開始取得下一個@splitter, 為位置A1,加@splitterlength長度(s.endpos + 2)後為位置B,從B位置到字串最後,判斷是否有@splitter,若有則重新Step2判斷.
--以此例為CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endpos + 2) > 0, 大於0表示新位置後還有@splitter
--Step3:取得Step1及Step2加總的結果集
測試SQL:
DECLARE @BOOK_TYPE TABLE (
BOOK_ID INT,
KEYWORD NVARCHAR(100),
BOOK_COUNT INT
)
INSERT INTO @BOOK_TYPE (BOOK_ID, KEYWORD, BOOK_COUNT) VALUES (1, N'雜誌,普科,月刊', 33),(2, N'書籍,園藝', 99),(3, N'書籍,語言', 11),(4, N'書', 11);
DECLARE @splitter VARCHAR(10) = ',';
DECLARE @splitterlength INT = LEN(@splitter) + 1;
;WITH BOOK_TYPE_LIST AS
(
--Step1:定義錨點(第一層)產生的資料集合
--以此例為位置0到第一次出現@splitter這段字串
SELECT
BOOK_ID,
1 AS startpos,
CHARINDEX(@splitter, KEYWORD + @splitter) - 1 AS endpos
FROM @BOOK_TYPE
WHERE LEN(KEYWORD) > 0
UNION ALL
--Step2:定義判斷條件(第二~N層)產生的資料集合
--loop:取得下一個@splitter位置A,加@splitterlength長度(s.endpos + 2)後位置為B,從B位置到字串最後,判斷是否有@splitter, 若有再重新Step2
--以此例為CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endpos + 2) > 0, 大於0表示還有@splitter
SELECT
s.BOOK_ID,
s.endpos + @splitterlength,
CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endpos + 2) - 1
FROM BOOK_TYPE_LIST s
JOIN @BOOK_TYPE m
ON s.BOOK_ID = m.BOOK_ID
AND CHARINDEX(@splitter, m.KEYWORD + @splitter, s.endpos + 2) > 0
)
--Step3:取得Step1及Step2加總的結果集
SELECT
m.BOOK_ID,
SUBSTRING(m.KEYWORD, s.startpos, s.endpos - s.startpos + 1) AS KEYWORD,
m.CLICK_COUNT
FROM @MENU_CLICK_COUNTER m
JOIN Split s ON m.BOOK_ID = s.BOOK_ID
ORDER BY m.BOOK_ID
參考:
https://dotblogs.com.tw/wasichris/2017/12/29/030655
https://ithelp.ithome.com.tw/questions/10191492
https://ithelp.ithome.com.tw/articles/10198214