{"id":595,"date":"2019-03-19T09:35:57","date_gmt":"2019-03-19T01:35:57","guid":{"rendered":"http:\/\/wordpress.cine.idv.tw\/?p=595"},"modified":"2019-03-19T09:40:58","modified_gmt":"2019-03-19T01:40:58","slug":"mysql-generate-table-contains-all-minutes-of-specific-date-dynamicall","status":"publish","type":"post","link":"https:\/\/wordpress.cine.idv.tw\/index.php\/2019\/03\/19\/mysql-generate-table-contains-all-minutes-of-specific-date-dynamicall\/","title":{"rendered":"MySQL \u52d5\u614b\u7522\u751f\u5305\u542b\u7279\u5b9a\u65e5\u671f\u6240\u6709\u5206\u9418\u7684\u8cc7\u6599\u8868"},"content":{"rendered":"\n

\u61c9\u7528\u7a0b\u5f0f\u5e38\u5e38\u6703\u6709\u6392\u7a0b\u7684\u9700\u6c42, \u5728\u8cc7\u6599\u5eab\u7684\u61c9\u7528\u4e2d, \u6392\u7a0b\u6240\u9700\u8981\u7684\u6642\u9593\u8868\u53ef\u4ee5\u7528\u4e0b\u5217\u9019\u5e7e\u7a2e\u65b9\u5f0f\u4f86\u7522\u751f:<\/p>\n\n\n\n

  1. \u4e8b\u5148\u7522\u751f\u597d\u61c9\u7528\u7a0b\u5f0f\u6240\u9700\u8981\u7684\u6642\u9593\u8868\u65bc\u5be6\u9ad4\u8cc7\u6599\u8868<\/li>
  2. \u5229\u7528\u67e5\u8868\u6cd5\u52d5\u614b\u7522\u751f\u865b\u64ec\u8cc7\u6599\u8868<\/li>
  3. \u5229\u7528\u6f14\u7b97\u6cd5\u7522\u751f\u865b\u64ec\u8cc7\u6599\u8868<\/li>
  4. <\/li><\/ol>\n\n\n\n

    shortie \u5c07\u65bc\u672c\u6587\u4e2d, \u793a\u7bc4\u7b2c\u4e09\u7a2e\u65b9\u6cd5\u5728 MySQL \u7684\u4e00\u7a2e\u61c9\u7528\u65b9\u5f0f. \u4e0b\u9762\u9019\u6bb5 SQL \u5c07\u6703\u7522\u751f\u67d0\u7279\u5b9a\u65e5\u671f\u4e2d\u7684 1440 (24 * 60) \u5206\u9418\u8cc7\u6599\u8868, \u7528\u4ee5\u8ddf\u5176\u4ed6\u8cc7\u6599\u8868 JOIN, \u9054\u6210\u61c9\u7528\u7a0b\u5f0f\u4e4b\u6240\u9700.<\/p>\n\n\n\n

    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;<\/pre>\n\n\n\n
    \u53c3\u8003\u8cc7\u6599<\/h4>\n\n\n\n