您好,登錄后才能下訂單哦!
這篇文章主要介紹了mysql如何生成連續(xù)日期及變量賦值,具有一定借鑒價(jià)值,感興趣的朋友可以參考下,希望大家閱讀完這篇文章之后大有收獲,下面讓小編帶著大家一起了解一下。
說(shuō)明:主要作用于一些統(tǒng)計(jì)數(shù)據(jù),來(lái)根據(jù)時(shí)間順序進(jìn)行顯示;
假如數(shù)據(jù)庫(kù)數(shù)據(jù)有隔天數(shù)據(jù),偏偏統(tǒng)計(jì)又需要每天的都顯示,即便是0,那就要生成一個(gè)時(shí)間表,來(lái)使用;
查詢數(shù)據(jù)庫(kù)數(shù)據(jù):
SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) AS date, COUNT(1) AS numb FROM qc_task WHERE create_time>= DATE_SUB(CURDATE(),INTERVAL 1 MONTH) and department_id IN ( SELECT id FROM `vigilante_jinan`.`qc_department` WHERE `area_code` = (@dep_BH) AND `status` = '1' AND dept_level >= (@dep_DJ)) GROUP BY DATE_FORMAT( create_time, '%Y-%m-%d' )
隔天時(shí)出數(shù)據(jù):
這樣是不是就不好看了,如果非要說(shuō)加個(gè)order by 1 desc 排一下,也可以…
但是就算排出來(lái),時(shí)間也是不連貫的;
生成最近7天的日期:
// 方法笨,但還是有效的 select DATE_FORMAT(SUBDATE(NOW(), interval (timeList.sj*1460) MINUTE),'%Y-%m-%d') as 'datetime' from (SELECT @num:=@num+1 as sj from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t, -- (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t1, -- 假如嫌棄生成的少,打來(lái)這個(gè)就行 (SELECT @num:=0) y) as timeList
生成的時(shí)間:
生成后使用:
-- 生成后左右鏈接即可,但主表要為時(shí)間表 SELECT f.datetime, t.numb FROM (SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) AS date, COUNT(1) AS numb FROM qc_task WHERE create_time>= DATE_SUB(CURDATE(),INTERVAL 1 MONTH) and department_id IN ( SELECT id FROM `vigilante_jinan`.`qc_department` WHERE `area_code` = (@dep_BH) AND `status` = '1' AND dept_level >= (@dep_DJ)) GROUP BY DATE_FORMAT( create_time, '%Y-%m-%d' )) t right join (select DATE_FORMAT(SUBDATE(NOW(), interval (timeList.sj*1460) MINUTE),'%Y-%m-%d') as 'datetime' from (SELECT @num:=@num+1 as sj from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) t, (SELECT @num:=0) y) as timeList) f on t.date = f.datetime ;
使用后效果:
我這里是數(shù)據(jù)庫(kù)沒有數(shù)據(jù)…所以看不到有數(shù)量顯示
看到第一個(gè)里面 @符號(hào)很多是嗎,那些都是變量,如果把查詢語(yǔ)句放進(jìn)去,整個(gè)查詢語(yǔ)句就顯得太臃腫了,會(huì)很長(zhǎng)…所以有些東西可以拆分出去;
比如在查詢數(shù)據(jù)庫(kù)語(yǔ)句中的:
來(lái)看下他們的真面目:
# 查詢部門區(qū)域編號(hào) SELECT @dep_BH:=(SELECT area_code FROM qc_department WHERE dept_name = "歷下區(qū)"); # 查詢部門等級(jí) SELECT @dep_DJ:=(SELECT dept_level FROM qc_department WHERE dept_name = "歷下區(qū)");
感謝你能夠認(rèn)真閱讀完這篇文章,希望小編分享的“mysql如何生成連續(xù)日期及變量賦值”這篇文章對(duì)大家有幫助,同時(shí)也希望大家多多支持億速云,關(guān)注億速云行業(yè)資訊頻道,更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。