回想距離上次鐵人賽到現在已經快一年了
後來工作經常用到蠻多 CTE 遞迴查詢
剛好在新的鐵人賽開始前另外筆記一下最近遇到蠻好用的需求:取得多組日期區間內的所有日期
可惜找不到方法補充在原系列文
有一個活動資料表,有三個欄位:活動名稱、活動開始日、活動結束日
活動頁面有一個行事曆,需要標示某個日期區間裡,有活動的所有日期
例如活動資料表當中,八月有這些活動:
活動名稱 | 活動開始日期 | 活動結束日期 |
---|---|---|
test01 | 2020-08-05 | 2020-08-09 |
test02 | 2020-08-07 | 2020-08-11 |
test03 | 2020-08-14 | 2020-08-16 |
八月期間,所有需要標示有舉辦活動的日期:
日期 |
---|
2020-08-05 |
2020-08-06 |
2020-08-07 |
2020-08-08 |
2020-08-09 |
2020-08-10 |
2020-08-11 |
2020-08-14 |
2020-08-15 |
2020-08-16 |
CREATE TABLE Item (
Item_No int PRIMARY KEY IDENTITY ,
ActivityName NVARCHAR(200),
StartDate DATETIME,
EndDate DATETIME,
)
INSERT INTO Item
(ActivityName, StartDate, EndDate)
VALUES
('test01', '2020-08-05', '2020-08-09'),
('test02', '2020-08-07', '2020-08-11'),
('test03', '2020-07-29', '2020-08-03'),
('test04', '2020-08-29', '2020-09-03'),
('test05', '2020-09-10', '2020-09-13'),
('test06', '2020-08-14', '2020-08-16')
WITH dates AS (
SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
FROM Item
WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
(EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
UNION ALL
SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
FROM dates
WHERE Date < EndDate
)
SELECT ActivityName,
CAST([Date] AS DATE) AS [Date],
CAST(StartDate AS DATE) AS StartDate,
CAST(EndDate AS DATE) AS EndDate
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY StartDate, [Date];
ActivityName | Date | StartDate | EndDate |
---|---|---|---|
test03 | 2020-08-01 | 2020-07-29 | 2020-08-03 |
test03 | 2020-08-02 | 2020-07-29 | 2020-08-03 |
test03 | 2020-08-03 | 2020-07-29 | 2020-08-03 |
test01 | 2020-08-05 | 2020-08-05 | 2020-08-09 |
test01 | 2020-08-06 | 2020-08-05 | 2020-08-09 |
test01 | 2020-08-07 | 2020-08-05 | 2020-08-09 |
test01 | 2020-08-08 | 2020-08-05 | 2020-08-09 |
test01 | 2020-08-09 | 2020-08-05 | 2020-08-09 |
test02 | 2020-08-07 | 2020-08-07 | 2020-08-11 |
test02 | 2020-08-08 | 2020-08-07 | 2020-08-11 |
test02 | 2020-08-09 | 2020-08-07 | 2020-08-11 |
test02 | 2020-08-10 | 2020-08-07 | 2020-08-11 |
test02 | 2020-08-11 | 2020-08-07 | 2020-08-11 |
test06 | 2020-08-14 | 2020-08-14 | 2020-08-16 |
test06 | 2020-08-15 | 2020-08-14 | 2020-08-16 |
test06 | 2020-08-16 | 2020-08-14 | 2020-08-16 |
test04 | 2020-08-29 | 2020-08-29 | 2020-09-03 |
test04 | 2020-08-30 | 2020-08-29 | 2020-09-03 |
test04 | 2020-08-31 | 2020-08-29 | 2020-09-03 |
WITH dates AS (
SELECT StartDate AS [Date], ActivityName, StartDate, EndDate
FROM Item
WHERE (StartDate >= '2020-08-01' AND StartDate <= '2020-08-31') OR
(EndDate >= '2020-08-01' AND EndDate <= '2020-08-31' )
UNION ALL
SELECT [Date] = DATEADD(DAY, 1, [Date]), ActivityName, StartDate, EndDate
FROM dates
WHERE Date < EndDate
)
SELECT DISTINCT CAST([Date] AS DATE) AS [Date]
FROM dates
WHERE [Date] >= '2020-08-01' AND [Date] <= '2020-08-31'
ORDER BY [Date]
Date |
---|
2020-08-01 |
2020-08-02 |
2020-08-03 |
2020-08-05 |
2020-08-06 |
2020-08-07 |
2020-08-08 |
2020-08-09 |
2020-08-10 |
2020-08-11 |
2020-08-14 |
2020-08-15 |
2020-08-16 |
2020-08-29 |
2020-08-30 |
2020-08-31 |
WITH recursive_sql (column_list)
AS
(
-- Anchor member
初始化 SELECT ...
UNION ALL
-- Recursive member that references recursive_sql.
遞迴查詢 SELECT...FROM recursive_sql
)
-- 呼叫遞迴查詢
SELECT *
FROM recursive_sql
初始化 SELECT
,取得所有的活動資料初始化 SELECT
得到的資料,將活動開始日+1代入遞迴查詢 SELECT
,開始遞迴查詢遞迴查詢 SELECT
時,[Date] < EndDate,遞迴查詢結果為空,結束遞迴查詢如果其他大大有更好的寫法,歡迎指教與提出建議
寫得不錯,例子也蠻好的.但是我有注意到,你的SQL Command 結束時都沒有使用 ;
似乎一些使用SQL Server 的,常會有此習慣.
我用 PostgreSQL 的 Set Returning Functions 其中
generate_series(start, stop, step interval)
https://www.postgresql.org/docs/current/functions-srf.html
注意到他是使用 timestamp or timestamptz, 所以我有使用轉型運算子 ::
這也是一種方式,提供補充參考.
create table it200812 (
id integer not null generated always as identity primary key
, actname text not null
, sdate date not null
, edate date not null
);
insert into it200812 (actname, sdate, edate) values
('test01', '2020-08-05', '2020-08-09'),
('test02', '2020-08-07', '2020-08-11'),
('test03', '2020-09-10', '2020-09-13'),
('test02', '2020-08-14', '2020-08-16');
select distinct generate_series(sdate::timestamp, edate, '1 day')::date as dt
from it200812
where sdate >= date '2020-08-01'
and sdate <= date '2020-08-31'
order by 1;
+------------+
| dt |
+------------+
| 2020-08-05 |
| 2020-08-06 |
| 2020-08-07 |
| 2020-08-08 |
| 2020-08-09 |
| 2020-08-10 |
| 2020-08-11 |
| 2020-08-14 |
| 2020-08-15 |
| 2020-08-16 |
+------------+
(10 rows)
謝謝屠豬大提供pg的SQL寫法
分號的部分聽大大一說,我去查一下官方文件後
才發現T-SQL大多數的陳述式(statement)結尾不需要使用分號
不過依照ANSI SQL-92的標準,結尾是需要加上分號
T-SQL的CTE的語法,也應該在WITH前面加上分號會比較好
這篇的例子,有多了9月部分來做對照,你是有用心安排的.
可以多寫一些來分享.另外PostgreSQLTW 有辦小講堂,
題目不限在PG,歡迎來分享一堂.
至於分號方面,這是隨你個人實際使用.我是有遇到一些不寫分號的,
然後要轉到其他資料庫時,會有些小小麻煩.
真的,像之前工作需要,寫PL/SQL的語法有發現語法嚴謹許多
有分號還有斜線
剛才發現還要考慮到跨月的問題(7跨8 與 8跨9),已經補上了
處理日期時間的問題,條件真的要寫的蠻嚴謹的
謝謝屠豬大的回饋