程式筆記

日期區間產生

測試

測試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