关联dim_date表，使日期连续

MYSQL:

1、报表需要展示连续每天的数据，但业务数据可能是间断的，故关联dim_date表，使日期连续

CREATE TABLE `dim_date` (

  `date_key` int(11) NOT NULL,

  `date_value` date DEFAULT NULL,

  `week_in_year` int(11) DEFAULT NULL,

  `week_in_month` int(11) DEFAULT NULL,

  `month_number` int(11) DEFAULT NULL,

  `quarter_number` int(11) DEFAULT NULL,

  `day_in_year` int(11) DEFAULT NULL,

  `day_in_month` int(11) DEFAULT NULL,

  `year` int(11) DEFAULT NULL,

  `month` int(11) DEFAULT NULL,

  `day` int(11) DEFAULT NULL,

  PRIMARY KEY (`date_key`),

  KEY `date_value_idx` (`date_value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、动态拼接年月日、然后分组

 

 

SELECT

<if test="type == 1">  

DATE(CONCAT(d.YEAR, '-', d.MONTH, '-', d.DAY)) 

   </if> 

<if test="type == 2">  

DATE(CONCAT(d.YEAR, '-', d.MONTH, '-1'))  

   </if> 

<if test="type == 3">  

DATE(CONCAT(d.YEAR, '-1-1')) 

   </if> 

<if test="type == 4">  

DATE(CONCAT(d.YEAR, '-', d.MONTH, '-', d.DAY)) 

   </if> 

   AS statdate,

SUM(a.order_num) AS order_num,

SUM(a.amount) AS amount,

SUM(a.validincome) AS validincome

FROM

dim_date d LEFT JOIN s_orderscount_day a on d.date_value = a.stat_date 

<if test="fcId=='' and groupId==null"> 

AND a.finance_category_id = -1  

</if>

<if test="fcId!='' or groupId!=null"> 

AND a.finance_category_id != -1  

</if>

 

<if test="channel != null and channel != '' "> 

AND a.sale_channel= #{channel}

</if>

<if test="fcId != null and fcId != ''"> 

AND FIND_IN_SET(a.finance_category_id, #{fcId})

</if>

<if test="fcId!='' or groupId!=null"> 

LEFT JOIN products p 

ON

a.finance_category_id = p.oldid  and a.product_group_id = p.groupid 

</if>

<if test="groupId != null and groupId != '' "> 

AND a.finance_category_id != -1 

AND p.groupid = #{groupId}

</if>

WHERE 1=1 

AND d.date_value <![CDATA[>=]]> #{startTime}

AND d.date_value <![CDATA[<=]]> #{endTime}

GROUP BY

statdate