應用程式常常會有排程的需求, 在資料庫的應用中, 排程所需要的時間表可以用下列這幾種方式來產生:
- 事先產生好應用程式所需要的時間表於實體資料表
- 利用查表法動態產生虛擬資料表
- 利用演算法產生虛擬資料表
shortie 將於本文中, 示範第三種方法在 MySQL 的一種應用方式. 下面這段 SQL 將會產生某特定日期中的 1440 (24 * 60) 分鐘資料表, 用以跟其他資料表 JOIN, 達成應用程式之所需.
SELECT ADDDATE(d.the_date, INTERVAL t.the_time MINUTE) AS the_date_time
FROM (SELECT '2019-03-18' the_date) AS d,
(SELECT the_hour * 60 + the_minute AS the_time
FROM (SELECT 0 AS the_hour
UNION
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
UNION
SELECT 10
UNION
SELECT 11
UNION
SELECT 12
UNION
SELECT 13
UNION
SELECT 14
UNION
SELECT 15
UNION
SELECT 16
UNION
SELECT 17
UNION
SELECT 18
UNION
SELECT 19
UNION
SELECT 20
UNION
SELECT 21
UNION
SELECT 22
UNION
SELECT 23) AS the_hours,
(SELECT the_ten * 10 + the_one AS the_minute
FROM (SELECT 0 AS the_ten
UNION
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5) AS tens,
(SELECT 0 AS the_one
UNION
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9) AS ones) AS the_minutes
ORDER BY the_time) AS t
ORDER BY the_date_time;