MYSQLは今日、昨日、今週、先週、今月、先月、今年、去年のデータを調べました.

一般的にバックグラウンドでレポートを作るとか、使うかもしれません.
createTime----作成時間は、比較する時間です.テーブルのフィールドタイプはdatetimeです.
ダイレクトコード

--         
-- SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())-1; 

--         
-- SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())

--          
-- select count(id) as count from user where date_format(createtime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') 

--          
-- SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m')=date_format(now(),'%Y-%m');
-- select count(id) as count from `user` where DATE_FORMAT(createtime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

--         6      
-- select count(id) as count from user where createtime between date_sub(now(),interval 6 month) and now(); 

--        
-- SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d');

--        
-- SELECT * FROM user WHERE TO_DAYS(NOW())-TO_DAYS(createTime) = 1

--    
-- select * from `user` where YEAR(createTime)=YEAR(NOW());
--    
-- select * from `user` where YEAR(createTime)=YEAR(NOW())-1;

--       
select 
    t1.count as toDay,
    tt1.count as lastDay,
    t2.count as lastWeek,
    tt2.count as toWeek,
    t3.count as lastMonth,
    tt3.count as toMonth,
    t4.count as toYear,
    tt4.count as lastYear,
    t.count as total
    from 
(SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')) t1,
(SELECT count(id) as count FROM user WHERE TO_DAYS(NOW())-TO_DAYS(createTime) = 1) tt1,
(SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())-1) t2,
(SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())) tt2,
(select count(id) as count from user where date_format(createtime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')) t3,
(SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m')=date_format(now(),'%Y-%m')) tt3,
(select count(id) as count from `user` where YEAR(createTime)=YEAR(NOW())) t4,
(select count(id) as count from `user` where YEAR(createTime)=YEAR(NOW())-1) tt4,
(select count(id) as count from user) t

現在の月、後12ヶ月、各月のデータを統計します.
次に、対照ビューの作成

CREATE
    ALGORITHM = UNDEFINED 
    DEFINER = `tyro`@`%` 
    SQL SECURITY DEFINER
VIEW `past_12_month_view` AS
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`

次に、統計したいテーブルに関連するクエリーを行います.次のdemoです.

select 
    v.month,
    ifnull(b.minute,0) count 
from 
    past_12_month_view v 
left join 
(select DATE_FORMAT(t.createTime,'%Y-%m') month,count(t.id) minute  from user t  group by month) b 
on 
    v.month = b.month 
group by 
    v.month

結果は次のとおりです.
QQ画像20170418160557.png
ついでに前回出会ったソートの小さな問題も書きます
データテーブルにsort_がありますnumフィールドはソートを表しますが、このフィールドにはnullの値があります.
現在のニーズは
sort_の場合、結果セットを昇順に返します.numがnullなら一番後ろに置く
mysql nullのデフォルトは最小値で、昇順で前に表示されます.
解決策

SELECT * from table_name 
ORDER BY 
  case 
WHEN 
  sort_num is null 
  then 
    1 
  else 0 end, sort_num asc

仕事が终わる...