<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ningdatech.carapi.car.rpt.mapper.RptMonthOverspeedSpecialCompanyMapper">

<select id="report" resultType="com.ningdatech.carapi.car.rpt.model.po.RptMonthSpecialVehiclesOverspeedPO">
    <!--根据传入的排名条件筛选对应的企业数据-->
    select
    tt.car_plate,
    tt.company_id,
    tt.company_name,
    tt.region_id,
    tt.overspeedTimes,
    tt.maxOverspeedRate,
    (@rank := @rank +1) as `rank`
    from
    <!--根据传入的(最高)超速率条件筛选对应的企业车辆数据-->
    (select
    st.car_plate,
    st.company_id,
    st.company_name,
    st.region_id,
    st.overspeedTimes,
    st.maxOverspeedRate
    from
    (select
    ft.car_plate,
    ft.company_id,
    ft.company_name,
    ft.region_id,
    ft.overspeedTimes,
    ft.maxOverspeedRate
    from
    <!--从车辆超速信息表中查出公司ID、公司名、区域ID、车辆总数、超速车辆数、超速率,（车辆）超速次数-->
    (select
    o.car_plate,
    o.company_id,
    c.name as company_name,
    o.region_id,
    count(o.id) as overspeedTimes,
    max(ifnull(round((o.max_speed - o.limit_speed) / o.limit_speed * 100,2),0)) as maxOverspeedRate
    from nd_vehicle_overspeed_info as o
    left join nd_vehicle_base_info as b on o.car_plate = b.car_plate
    left join nd_region as r on o.region_id = r.id
    left join nd_company_info as c on o.company_id = c.id
    where o.deleted = 0 and o.max_speed is not null and o.limit_speed is not null and
    b.deleted = 0 and b.audit_state = 2 and b.vehicle_type in (1,2,3,4)
    <if test="param.startTime != null and param.endTime != null">
        and o.overspeed_date
        between concat(#{param.startTime},' 00:00:00') and concat(#{param.endTime},' 23:59:59')
    </if>
    ${param.dataScopeSql}
    group by o.car_plate,o.company_id,c.name,o.region_id
    order by maxOverspeedRate desc,overspeedTimes desc
    ) as ft
    where 1 = 1
    <if test="param.startRate != null">
        and maxOverspeedRate &gt;= #{param.startRate}
    </if>
    <if test="param.endRate != null">
        and maxOverspeedRate &lt;= #{param.endRate}
    </if>
    ) as st,
    (select @rank := 0) as r
    ) as tt
    where 1 = 1
    <if test="param.rank != null">
        and @rank &lt; #{param.rank}
    </if>
</select>
</mapper>
