OTA-数据完整性分析【生产者车辆升级过程完整性分析】修改为全部数据排名

qq_41868957

已于 2022-06-06 09:21:20 修改

阅读量195

点赞数

分类专栏： zq-o 文章标签： postman 测试工具

于 2022-06-02 14:08:04 首次发布

本文链接：https://blog.csdn.net/qq_41868957/article/details/125099681

版权

zq-o 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

这个SQL查询用于从多个表格中联合分析汽车日志的完整性。它通过LEFT JOIN操作连接`car_corp`表和每个`tableNameList`中的表格，并使用IFNULL函数处理可能为NULL的日志总数。查询还包含条件过滤，如开始和结束日期，以获取特定时间段内的不完整记录数和日志总数。最后，根据不完整记录数进行排序并限制返回结果的前10条记录。

摘要由CSDN通过智能技术生成

CarCorpMapper.xml

IFNULL(temp.logTotal,0)

<select id="carLogIntegrityAnalysis" resultMap="carLogIntegrityAnalysisMap">
        SELECT cc.corp_cname,IFNULL(temp.logTotal,0) as logTotal,IFNULL(temp.integrityLogNums,0) as integrityLogNums,temp.incomcnt,temp.type
        FROM car_corp cc
        LEFT JOIN
        (
        <foreach collection="tableNameList" item="tableName" separator="UNION">
            (
            SELECT
            t.*
            , ( SELECT COUNT(*) FROM ota_report_flow as orf LEFT JOIN ${tableName} as l ON orf.record_num = l.record_num
            ANd orf.audit_state = 2
            WHERE l.incomp_field IS NOT NULL AND l.incomp_field != ''
            <if test="@com.zhuoqin.common.utils.Ognl@isNotEmpty(query.beginDate)">and orf.report_time &gt;=
                DATE_FORMAT(#{query.beginDate}, '%Y-%m-%d')
            </if>
            <if test="
            @com.zhuoqin.common.utils.Ognl@isNotEmpty(query.endDate)">and orf.report_time &lt;=
                DATE_FORMAT(#{query.endDate}, '%Y-%m-%d')
            </if>
                ) as integrityLogNums
                , (
                    SELECT COUNT(*) FROM
                    ota_report_flow as orf LEFT JOIN ${tableName} as l ON orf.record_num = l.record_num
                    ANd orf.audit_state = 2
                    <where>
                        <if test="@com.zhuoqin.common.utils.Ognl@isNotEmpty(query.beginDate)">
                            and l.upload_time &gt;= DATE_FORMAT(#{query.beginDate}, '%Y-%m-%d')
                        </if>
                        <if test="@com.zhuoqin.common.utils.Ognl@isNotEmpty(query.endDate)">
                            and l.upload_time &lt;= DATE_FORMAT(#{query.endDate}, '%Y-%m-%d')
                        </if>
                    </where>
                ) as logTotal
                ,COUNT(t.vin_num) as incomcnt
                FROM (
                    SELECT DISTINCT
                    orf.record_num
                    ,ci.car_nums
                    ,l.vin_num
                    ,cc.corp_cname
                    ,substring_index( substring_index( l.incomp_field, ',', rr.id + 1 ), ',',-1 ) AS type
                    FROM ota_report_flow as orf
                    LEFT JOIN ${tableName} as l ON l.record_num = orf.record_num
                    LEFT JOIN row2rows as rr ON rr.id &lt; ( length( l.incomp_field ) - length( REPLACE ( l.incomp_field, ',',
                    '' )) + 1 )
                    LEFT JOIN car_corp as cc on cc.cpid = orf.cpid
                    LEFT JOIN car_info as ci ON ci.record_num = orf.record_num
                    WHERE orf.audit_state = 2
                    AND l.incomp_field IS NOT NULL AND l.incomp_field != ''
                    <if test="@com.zhuoqin.common.utils.Ognl@isNotEmpty(query.beginDate)">
                        and orf.report_time &gt;= DATE_FORMAT(#{query.beginDate}, '%Y-%m-%d')
                    </if>
                    <if test="@com.zhuoqin.common.utils.Ognl@isNotEmpty(query.endDate)">
                        and orf.report_time &lt;= DATE_FORMAT(#{query.endDate}, '%Y-%m-%d')
                    </if>
                ) as t
                GROUP BY type
                ORDER BY incomcnt ${query.sortType}
                LIMIT 10
            )

        </foreach>
         )temp
        on cc.corp_cname = temp.corp_cname
    </select>