問題：需要傳入一個時間范圍(比如2017-07-20阿蝶，2017-07-31)，查詢表A,B,C每一天的記錄數(shù),這里聯(lián)合查詢應(yīng)當(dāng)用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'羡洁，這樣當(dāng)A在這一天沒有記錄，但是B或C有爽丹，這一天也會有記錄筑煮。
但是所用數(shù)據(jù)庫是mysql，不支持full join粤蝎。還有一個問題就是即使用full join真仲，如果這幾個表都沒有符合條件的記錄，這一天也沒有記錄诽里，當(dāng)然這個代碼層面可以處理袒餐。
解決思路：
不支持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
但是這樣的結(jié)果是
image.png
很明顯，這些數(shù)據(jù)的別名應(yīng)該是分開的谤狡，修改為
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
還不符合要求灸眼，我們需要的是一行數(shù)據(jù)，而不是三行墓懂，這里使用一個小技巧焰宣，用sum來合并這三行數(shù)據(jù)
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
成功了一半，我們需要每天這幾張表的新增記錄數(shù)捕仔，即使這一天一個新增記錄也沒有
由于數(shù)據(jù)庫并沒有日期表匕积，所以需要自己得到日期集合
考慮在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("時間參數(shù)不正確", 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
