Sql语句查询近一周内每天对应的记录数，要求返回日期，数据为空时以默认值填充（mysql数据库）

本文链接：https://blog.csdn.net/qq_42751248/article/details/103938945

本文介绍了一种SQL查询方法，用于统计近一周内用户发布的投资信息收到的留言数量，即使某天无留言也能以默认值填充，确保数据连续性。采用左连接和IFNULL函数实现数据完整展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、要求和效果图：查询出近一周内符合的日期和每天对应的数据，要求返回近七天的日期，且支持联表查询，效果图如下：

二、具体事例：通过留言表需查询出近一周内当前用户发布的投资信息收到的留言数，要求返回符合近七天的日期和每天对应的数据，如果每天没有查询到数据，以默认值进行填充，IFNULL(显示数据,默认值)函数可实现，且日期是以升序进行排列，具体sql如下：

/* 
  sql语句事例 
  leave_message：留言表 
  fncl_ivt：投资表 
  ivt_person_code：发布投资信息人的编码 
*/

SELECT a.create_time,IFNULL(b.count,0) AS COUNT
FROM (
    SELECT CURDATE() AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS create_time
) a LEFT JOIN (
  SELECT DATE(l.create_time) AS create_time, COUNT(*) AS COUNT, f.ivt_person_code
   FROM leave_message l LEFT JOIN fncl_ivt f ON l.fncl_id = f.ivt_id WHERE f.ivt_person_code = 2
   GROUP BY DATE(l.create_time) 
) b ON a.create_time = b.create_time ORDER BY a.create_time ASC;

三、mybatis的xml文件里的写法如下：

 <!-- xml文件写法：查询近一周每天收到的投资留言数 -->
 <select id="getReceiveIvtLeaMsgNumber" parameterType="Long" resultType="java.util.Map">
    SELECT a.create_time, IFNULL(b.count,0) AS COUNT
	FROM (
	 SELECT CURDATE() AS create_time
	 UNION ALL
	 SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS create_time
	 UNION ALL
	 SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS create_time
	 UNION ALL
	 SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS create_time
	 UNION ALL
	 SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS create_time
	 UNION ALL
	 SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS create_time
	 UNION ALL
	 SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS create_time
    ) a LEFT JOIN (
	 SELECT DATE(l.create_time) AS create_time, COUNT(*) AS COUNT, f.ivt_person_code
	 FROM leave_message l LEFT JOIN fncl_ivt f ON l.fncl_id = f.ivt_id WHERE 
	 f.ivt_person_code = #{ivtPersonCode} GROUP BY DATE(l.create_time) 
    ) b ON a.create_time= b.create_time order by a.create_time ASC;
</select>

四：参考链接：https://blog.csdn.net/ouyang111222/article/details/77638826