给定一个年月 获取当月的所有日期

比眉伴天荒 2021-09-07 06:09 611阅读 0赞

记录一下 给定一个年月 获取当月的所有日期

  1. SELECT
  2.     da.das AS das
  3.   ,IFNULL( sum( t1.date ), 0 ) AS number 
  4. FROM
  5.     ( SELECT ( date_add( date_format(now(), '%Y-%m-%d' ), INTERVAL ( cast( help_topic_id AS signed ) - 31 ) DAY ) ) das FROM    mysql.help_topic ORDER BY help_topic_id ) da
  6.       LEFT JOIN ( 
  7.             SELECT COUNT(t1.habit_start_date) AS date,t1.habit_start_date AS start_date,t1.habit_end_date AS end_date FROM sch_teacher_publish_habit t1 
  8.             LEFT JOIN sch_class t2 ON t2.deleted = 0 AND t2.user_id = '0ea0bc482d652da8718e0e682c8faf51' AND FIND_IN_SET(t1.class_id,t2.id)
  9.             WHERE t1.deleted = 0
  10.             ) t1 ON da.das BETWEEN date_format( t1.start_date, '%Y-%m-%d' ) AND date_format( t1.end_date, '%Y-%m-%d' ) 
  11. WHERE
  12.     ( date_format( da.das, '%Y-%m' ) = '2021-02' OR date_format( da.das, '%Y-%m' ) = '2021-02' ) 
  13. GROUP BY    da.das 
  14. ORDER BY da.das

上述示例展示的是 left join里面关联的是数据库中的表 该sql 返回值为 

  1. List<Map<String, Object>>

发表评论

表情：
评论列表 （有 0 条评论，611人围观）

还没有评论，来说两句吧...

相关阅读