统计随笔

1、按年、按月、按日统计

    <!--保函销售情况分析 1按年  2按月 3按日-->
    <select id="getBHSellStatics" resultType="map">

        <if test="timeType != null and timeType == 1">
            <![CDATA[
        select LEFT(CONVERT(VARCHAR(10), project.API_OPEN_TIME, 120), 4) as times,count(trade.ORDER_SN) issueCount
        from T_TRADE as trade
            left join T_PROJECT as project on project.PROJECT_ID = trade.PROJECT_ID
            left join T_ISP  as isp on isp.DATAID = trade.ISP_GUID
        where trade.STATUS = 3
          and project.API_OPEN_TIME >= #{startTime}
          and project.API_OPEN_TIME <= #{endTime}
        group by LEFT(CONVERT(VARCHAR(10), project.API_OPEN_TIME, 120),4)
        ]]>
        </if>

        <if test="timeType != null and timeType == 2">
            <![CDATA[
        select LEFT(CONVERT(VARCHAR(10), project.API_OPEN_TIME, 120), 7) as times,count(trade.ORDER_SN) issueCount
        from T_TRADE as trade
            left join T_PROJECT as project on project.PROJECT_ID = trade.PROJECT_ID
            left join T_ISP  as isp on isp.DATAID = trade.ISP_GUID
        where trade.STATUS = 3
          and project.API_OPEN_TIME >= #{startTime}
          and project.API_OPEN_TIME <= #{endTime}
        group by LEFT(CONVERT(VARCHAR(10), project.API_OPEN_TIME, 120), 7)
        ]]>
        </if>

        <if test="timeType != null and timeType == 3">
            <![CDATA[
        select LEFT(CONVERT(VARCHAR(10), project.API_OPEN_TIME, 120), 10) as times,count(trade.ORDER_SN) issueCount
        from T_TRADE as trade
            left join T_PROJECT as project on project.PROJECT_ID = trade.PROJECT_ID
            left join T_ISP  as isp on isp.DATAID = trade.ISP_GUID
        where trade.STATUS = 3
          and project.API_OPEN_TIME >= #{startTime}
          and project.API_OPEN_TIME <= #{endTime}
        group by LEFT(CONVERT(VARCHAR(10), project.API_OPEN_TIME, 120), 10)
        ]]>
        </if>


    </select>