问题：需要传入一个时间范围(比如2017-07-20，2017-07-31)，查询表A,B,C每一天的记录数,这里联合查询应当用full join的，即A full join B on A.date=B.date full join C on A.date=C.date where A.date between '2017-07-20' and '2017-07-31'，这样当A在这一天没有记录，但是B或C有，这一天也会有记录。
但是所用数据库是mysql，不支持full join。还有一个问题就是即使用full join，如果这几个表都没有符合条件的记录，这一天也没有记录，当然这个代码层面可以处理。
解决思路：
不支持full join就逐表查然后用union all合并
SELECT count(1) as A_count from A
UNION ALL
SELECT count(1) as B_count from B
UNION ALL
SELECT count(1) as C_count from C
但是这样的结果是
image.png
很明显，这些数据的别名应该是分开的，修改为
SELECT count(1) as A_count,0 as B_count,0 as C_count from A
UNION ALL
SELECT 0 as A_count,count(1) as B_count,0 as C_count from B
UNION ALL
SELECT 0 as A_count,0 as B_count,count(1) as C_count from C
image.png
还不符合要求，我们需要的是一行数据，而不是三行，这里使用一个小技巧，用sum来合并这三行数据
SELECT sum(A_count) A_count ,sum(B_count) B_count,sum(C_count) C_count from (
SELECT count(1) as A_count,0 as B_count,0 as C_count from A
UNION ALL
SELECT 0 as A_count,count(1) as B_count,0 as C_count from B
UNION ALL
SELECT 0 as A_count,0 as B_count,count(1) as C_count from C
) t
image.png
成功了一半，我们需要每天这几张表的新增记录数，即使这一天一个新增记录也没有
由于数据库并没有日期表，所以需要自己得到日期集合
考虑在java层得到这个时间段的每一天{'2017-07-20","2017-07-21","2017-07-22"......''2017-07-31"},然后传入mybatis，遍历生成sql语句。
List<String> dates = new ArrayList<>();
dates.add(startDate);
try {
Date dateOne = dateFormat.parse(startDate);
Date dateTwo = dateFormat.parse(endDate);
Calendar calendar = Calendar.getInstance();
calendar.setTime(dateOne);
while (calendar.getTime().before(dateTwo)) {
dates.add((dateFormat.format(calendar.getTime())));
calendar.add(Calendar.DAY_OF_MONTH, 1);
}
} catch (Exception e) {
logger.warn("时间参数不正确", e);
return null;
}
dates.add(endDate);
List<Map<String, Object>> list = mapper.selectWarning(dates);
mapper.java
List<Map<String, Object>> selectWarning(@Param(value="dates")List<String> dates);
mapper.xml
<select id="selectWarning" resultType="java.util.HashMap">
<foreach collection="dates" item="date" index="index" open="" separator=" union " close="">
SELECT #{date} as date,sum(A_count) A_count ,sum(B_count) B_count,sum(C_count) C_count from (
SELECT count(1) as A_count,0 as B_count,0 as C_count from A where date(create_date)=#{date}
UNION ALL
SELECT 0 as A_count,count(1) as B_count,0 as C_count from B where date(create_date)=#{date}
UNION ALL
SELECT 0 as A_count,0 as B_count,count(1) as C_count from C where date(create_date)=#{date}
) t
</foreach>
order by date desc
</select>
image.png
