按日，周，年统计，无的数据补充0

需求：按日-周-年统计。统计涉及到3张表数据。
在这里插入图片描述

写sql。先把3张表数据摘取出来，只需对3张表的时间做分组统计即可。
在这里插入图片描述
按日统计

select DAY(dateff) as time,IFNULL(count(id),0)as num
from(select create_time as dateff,id as id from cz_taxi_orders UNION ALL
select create_time as dateff,id as id from ctky_passenger_transport_order UNION ALL
select pay_time as dateff,id as id from bus_order) as a
GROUP BY time

按年统计

#按年 需求:统计最近十年数据
select YEAR(dateff) as time,IFNULL(count(id),0)as num
from(select create_time as dateff,id as id from cz_taxi_orders UNION ALL
select create_time as dateff,id as id from ctky_passenger_transport_order UNION ALL
select pay_time as dateff,id as id from bus_order) as a
WHERE YEAR(dateff)>year(now())-10
GROUP BY time

按周统计

select 
CASE temp.time 
WHEN 1 THEN '星期一' 
WHEN 2 THEN '星期二' 
WHEN 3 THEN '星期三' 
WHEN 4 THEN '星期四' 
WHEN 5 THEN '星期五' 
WHEN 6 THEN '星期六' 
WHEN 7 THEN '星期天' 
end as time,
IFNULL(temp2.num,0) as num 
from(
select 1 as time UNION all
SELECT 2 UNION all
SELECT 3 UNION all
SELECT 4 UNION all
SELECT 5 UNION all
SELECT 6 UNION all
SELECT 7) as temp
LEFT JOIN 
(select ifNULL(count(id),0) as num, 
CASE WEEKDAY(dateff) 
WHEN 0 THEN '1' 
WHEN 1 THEN '2' 
WHEN 2 THEN '3' 
WHEN 3 THEN '4' 
WHEN 4 THEN '5' 
WHEN 5 THEN '6' 
WHEN 6 THEN '7' 
END as `time` 
from(
SELECT create_time as dateff,id as id FROM cz_taxi_orders UNION ALL
SELECT create_time as dateff,id as id FROM ctky_passenger_transport_order UNION ALL
SELECT pay_time as dateff,id as id FROM bus_order) as a 
GROUP BY `time`) as temp2
ON temp.time = temp2.time 
ORDER BY temp.time asc;

组装动态sql。

<select id="queryPassengerFlow" resultType="com.xxxCockpitPassengerFlowChartVo">
        <if test="type=='WEEK'">
            select
            CASE temp.time
            WHEN 1 THEN '星期一'
            WHEN 2 THEN '星期二'
            WHEN 3 THEN '星期三'
            WHEN 4 THEN '星期四'
            WHEN 5 THEN '星期五'
            WHEN 6 THEN '星期六'
            WHEN 7 THEN '星期天'
            end as time,IFNULL(temp2.num,0) as num
            from(select 1 as time UNION all select 2 UNION all
            select 3 UNION all select 4 UNION all
            select 5 UNION all select 6 UNION all
            select 7) as temp
            LEFT JOIN
            (select ifNULL(count(id),0) as num,
            CASE WEEKDAY(dateff)
            WHEN 0 THEN '1'
            WHEN 1 THEN '2'
            WHEN 2 THEN '3'
            WHEN 3 THEN '4'
            WHEN 4 THEN '5'
            WHEN 5 THEN '6'
            WHEN 6 THEN '7'
            END as `time`
        </if>
        <if test="type=='DAY'">
            select count(id) as num,
            DAY(dateff) as `time`
        </if>
        <if test="type=='YEAR'">
            select YEAR(dateff) as `time`,
            count(id) as num
        </if>
        from(select create_time as dateff,id as id from cz_taxi_orders UNION ALL
        select create_time as dateff,id as id from ctky_passenger_transport_order UNION ALL
        select pay_time as dateff,id as id from bus_order) as a
        <if test="type=='WEEK'">
            GROUP BY `time`) as temp2
            on temp.time = temp2.time
            ORDER BY temp.time asc
        </if>
        <if test="type=='DAY'">
            GROUP BY `time`
        </if>
        <if test="type=='YEAR'">
            WHERE (YEAR(dateff))>year(now())-10
            GROUP BY `time`
        </if>
    </select>

剩余的年，日，用java代码补充。

@Override
    public R queryPassengerFlow(String type) {
    //一开始加载，不输入默认按天
        if (type==null){
            type="DAY";
        }
        List<CockpitPassengerFlowChartVo> list = cockpitChartMapper.queryPassengerFlow(type);
        List<CockpitPassengerFlowChartVo> list1 = new ArrayList<>();
        //年查询递推十年
        List<CockpitPassengerFlowChartVo> list2 = new ArrayList<>();
        if ("YEAR".equals(type)){
            Calendar cal = Calendar.getInstance();
            int currentYear = cal.get(Calendar.YEAR);
            cal.set(Calendar.YEAR,currentYear-9);
            int toYear = cal.get(Calendar.YEAR);
            for (int i = currentYear; i >= toYear; i--) {
                CockpitPassengerFlowChartVo vos = new CockpitPassengerFlowChartVo();
                boolean flag = false;
                for (CockpitPassengerFlowChartVo vo : list) {
                    if (vo.getTime().equals(String.valueOf(i))) {
                        flag = true;
                        //月份
                        vos.setTime(vo.getTime());
                        //数量
                        vos.setNum(vo.getNum());
                    }
                }
                //月份不存在 赋值0
                if (!flag) {
                    vos.setTime(String.valueOf(i));
                    vos.setNum(0);
                }
                list1.add(vos);
            }
            return R.ok(list1);
        }
        if ("DAY".equals(type)){
            for (int i = 1; i <=30 ; i++) {
                CockpitPassengerFlowChartVo vos = new CockpitPassengerFlowChartVo();
                boolean flag = false;
                for (CockpitPassengerFlowChartVo vo : list) {
                    if (vo.getTime().equals(String.valueOf(i))) {
                        flag = true;
                        //月份
                        vos.setTime(vo.getTime());
                        //数量
                        vos.setNum(vo.getNum());
                    }
                }
                //月份不存在 赋值0
                if (!flag) {
                    vos.setTime(String.valueOf(i));
                    vos.setNum(0);
                }
                list2.add(vos);
            }
            return R.ok(list2);
        }
        return R.ok(list);
    }