個人使用向5:連續時間表的應用

浮華人世的浮華發表於2020-12-09

場景：某些sql需要關聯時間進行排序。好傢伙，order by time 就完事了，ASC 或者 DESC。然後說要連續時間？？？單純按時間是很簡單，但是某些時間是會發生斷層，但是需求他想要。那麼開整。

過程： A.本身資料是攜帶某個時間標識，新增時間或者修改時間都是必備的。但是這個沒辦法說一定是連續的，能保證先後順序。所以需要輔助工具。是否我在不動表情況下，去搞輔助手段。比如我傳連續的時間list,進去，用$符號就硬拼接當一個結果集，然後結果集別名其他表，作為資料進行時間聯動關聯。是個辦法，但是每次進行查詢，都要給你整這個就很麻煩。另外時間list 首先要確定起始時間跟結束時間，可以前端傳值，但是如果有人搞你的話，來個1970-9999。你可以大膽一些，覺得你for迴圈去處理起始時間到截止時間的 年月日資料會怎麼樣。是不是想想就很嗨，所以這個實現簡單，思路還行，但是禁不起做。那麼還是資料放到資料庫中處理。從而多一張時間型別的表。就記錄連續的年月日。衝！

步驟:當然是百度，然後複製，加一些自己理解，然後就是一篇自己的分享了。

1.建立一個num表，用來儲存數字0~9

CREATE TABLE num (i int);

在這裡插入圖片描述

輔助的num表，用來儲存數字，過後要刪的

2.在num表中生成0~9

-- CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

在這裡插入圖片描述

成果展示

在這裡插入圖片描述

3.生成一個儲存日期的表，datalist是欄位名

-- CREATE TABLE num (i int);
-- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
CREATE TABLE  if not exists calendar(datelist date);

在這裡插入圖片描述

在這裡插入圖片描述

4.生成並插入日期資料

-- CREATE TABLE num (i int);
-- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
-- CREATE TABLE  if not exists calendar(datelist date);
INSERT INTO calendar(datelist) SELECT
    adddate(
        (   -- 這裡的起始日期，你可以換成當前日期
            DATE_FORMAT("2016-1-1", '%Y-%m-%d')
        ),
        numlist.id
    ) AS `date`
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;

我換成2020-1-1

在這裡插入圖片描述

不多說 10w天 快3百年

在這裡插入圖片描述

夠用了。

5.最後再新增主鍵即可

-- CREATE TABLE num (i int);
-- INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
-- CREATE TABLE  if not exists calendar(datelist date);
-- INSERT INTO calendar(datelist) SELECT
--     adddate(
--         (   -- 這裡的起始日期，你可以換成當前日期
--             DATE_FORMAT("2016-1-1", '%Y-%m-%d')
--         ),
--         numlist.id
--     ) AS `date`
-- FROM
--     (
--         SELECT
--             n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
--         FROM
--             num n1
--         CROSS JOIN num AS n10
--         CROSS JOIN num AS n100
--         CROSS JOIN num AS n1000
--         CROSS JOIN num AS n10000
--     ) AS numlist;
ALTER TABLE `calendar`
ADD COLUMN `id`  int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵' FIRST ,
ADD PRIMARY KEY (`id`);

加上主鍵
在這裡插入圖片描述

在這裡插入圖片描述

最終結果如圖所示，然後再去統計資料時候 關聯該日期表就行了。

以上上你能百度到的。我說點我專案中真的引用到的。

A.關聯

SELECT
	c.datelist AS datelist,
	a.*
FROM
	calendar c
	LEFT OUTER JOIN XXX_a a ON date_format( c.datelist, '%Y-%m-%d' ) = date_format( a.scheduling_time, '%Y-%m-%d' ) 
WHERE
	a.STATUS = 1 < IF test = "ids != null and ids != '' " > 
	AND a.user_id IN ( $ { ids } ) </ IF > < IF test = "startOlddate != null and startOlddate != '' " > 
	AND date_format( c.datelist, '%Y-%m-%d' ) <![ CDATA [ >= ]]> #{startOlddate}   </if>
	< IF test = "endOlddate != null and endOlddate != '' " > 
	AND date_format( c.datelist, '%Y-%m-%d' ) <![ CDATA [ <= ]]> #{endOlddate} </if>
ORDER BY
	c.datelist

大意是a表是我們原先具體的業務表，然後c是剛剛建立的連續時間輔助表，通過時間進行關聯。因為時間要連續的，所以左關聯還是右關聯這個問題注意一下。然後通過格式轉換進行年月日格式對比。

B.後續甲方給我提了一個節假日問題的。

大致是 希望能在日期上面顯示 國慶節快樂，這些花裡胡哨的。

方案1:獲取網上的，這裡我直接上我那時候寫的程式碼

package com.bcloud.iotattendance.util;

import com.alibaba.fastjson.JSONObject;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;

import java.io.InputStream;

import java.net.HttpURLConnection;
import java.net.URL;
import java.util.HashMap;
import java.util.Map;

/**
 * create by: fuhua
 * description: 對應參考網址為  http://timor.tech/api/holiday
 * create time: 2020/8/25 0025 9:53
 *
  * @Param: null
 * @return
 */

public class HolidayUtil {


    private final static String httpUrl="http://timor.tech/api/holiday/batch";

    /**

     * @param httpArg

     *            :引數 具體日期 比如  2020-10-10  或者 2020-10-01  單日更新

     * @return 返回結果

     */

    public static Map<String, Object> request(String httpArg) {


        String httpUrl="http://timor.tech/api/holiday/info";

        BufferedReader reader = null;

        String result = null;

        StringBuffer sbf = new StringBuffer();

        httpUrl = httpUrl + "/" + httpArg;

        Map<String ,Object> mapHoliday = new HashMap<>();

        try {

            URL url = new URL(httpUrl);

            HttpURLConnection connection = (HttpURLConnection) url

                    .openConnection();
            connection.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");

            connection.setRequestMethod("GET");

            connection.connect();

            InputStream is = connection.getInputStream();

            reader = new BufferedReader(new InputStreamReader(is, "UTF-8"));

            String strRead = null;

            while ((strRead = reader.readLine()) != null) {

                sbf.append(strRead);

                sbf.append("\r\n");

            }

            reader.close();

            result = sbf.toString();

            JSONObject ob= JSONObject.parseObject(result);

            if(ob!=null){
                //code 1標識 正常  3有假期  -1 標識異常  2.法定調休
                int code=Integer.parseInt(ob.getString("code"));
                if(code == 0 ){
                    mapHoliday.put("code",1);
                    if(ob.getString("holiday")!=null){
                    mapHoliday.put("code",3);
                    JSONObject holidayObject= JSONObject.parseObject(ob.getString("holiday"));
                    //true表示是節假日，false表示是調休
                    mapHoliday.put("holiday",holidayObject.getBoolean("holiday"));
                    //true表示是節假日名稱
                    mapHoliday.put("name",holidayObject.getString("name"));
                    //薪資倍數，1表示是1倍工資也就是正常，3倍工資
                    mapHoliday.put("wage",holidayObject.getIntValue("wage"));
                    if(holidayObject.get("after")!=null){
                        mapHoliday.put("code",2);
                    }
                    //只在調休下有該欄位。true表示放完假後調休，false表示先調休再放假
                    mapHoliday.put("after",holidayObject.getBoolean("after"));
                    // 只在調休下有該欄位。表示調休的節假日
                    mapHoliday.put("target",holidayObject.getString("target"));
                    }
                }else{
                    mapHoliday.put("code",99);
                }
            return mapHoliday;
            }
        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }
}

2.但是你架不住沒外網啊，或者呼叫很頻繁。

沒外網，那麼你遠端呼叫其他介面，都是失敗的，沒網還能怎麼辦。呼叫頻繁，官方的處理是，這個很難辦，得加錢。本來就是覺得小功能，白嫖去的，甲方應該不想加錢。所以可以在上面的日期表進行擴充。我加點標識這些日期的是什麼節日名稱顯示，這樣不就行了嗎。然後就是簡單的單表增刪改查，開放給客戶自己維護，自動維護你又沒網，開發不能天天給你弄這個吧，所以開放介面你自己也能時間。最後表資料

CREATE TABLE `calendar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `datelist` date DEFAULT NULL COMMENT '年月日',
  `holiday` varchar(64) DEFAULT '' COMMENT '節假日名稱',
  `type` char(1) DEFAULT '0' COMMENT '型別:0 正常 1.週末  2.節假日 3.人工修改',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

這只是大概思路，有一些東西可以自己瞭解之後，加點細節，就是一個需求的便捷完成了。

程式碼都在上面了，所以就沒有百度雲了

漫漫長路，一個小周跟他一個小陳朋友一起努力奔跑。

相關文章