mysql 查询往前第三个月的数据

<select id="countList" resultMap="BusinessUserResult">
        SELECT
        u.develope_num,
        (SELECT
        round(sum(a.product_assets*b.real_rate),2)
        FROM
        business_user a
        LEFT JOIN business_receipts b ON a.customer_num = b.customer_num
        AND a.order_time = b.order_time
        AND a.product_id = b.product_id
        WHERE
        develope_num = u.develope_num
        and DATE_FORMAT(b.bill_date,'%Y-%m') =DATE_FORMAT(date_sub(#{inputDate}, interval 3 month),'%Y-%m')
        and  TIMESTAMPDIFF(MONTH,a.order_time,DATE_FORMAT(#{inputDate}, '%Y-%m-%d')) between 0 and 12 ) as clear,

        (
        SELECT
        round(sum(b.return_assets),2)
        FROM
        business_user a
        LEFT JOIN business_return b ON a.customer_num = b.customer_num
        AND a.order_time = b.order_time
        AND a.product_id = b.product_id
        WHERE
        develope_num = u.develope_num and
        b.return_date =DATE_FORMAT(#{inputDate},'%Y-%m')
        ) as returns

        FROM
        business_user u
        <where>
            <if test="developeNum != null  and developeNum != ''"> 
            and develope_num = #{developeNum}
            </if>
        </where>
        group by u.develope_num

    </select>


```





```/
select ifnull(round(sum(retail_sales_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `name`,'零售'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(retail_sales_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `name`,'零售'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(retail_sales_volume),0),0)as 'value', DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `name`,'零售'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(retail_sales_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `name`,'零售'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(retail_sales_volume),0),0) as 'value', DATE_FORMAT(CURDATE(), '%Y-%m') AS `name`,'零售'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT(CURDATE() , '%Y-%m-%d'))=year and month(DATE_FORMAT(CURDATE() , '%Y-%m-%d'))=month union select ifnull(round(sum(wholesale_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `name`,'批发'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(wholesale_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `name`,'批发'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(wholesale_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `name`,'批发'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(wholesale_volume),0),0) as 'value', DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `name`,'批发'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=month union select ifnull(round(sum(wholesale_volume),0),0) as 'value', DATE_FORMAT(CURDATE(), '%Y-%m') AS `name`,'批发'as 'series' from hp_commerce_social_consumer_goods where year(DATE_FORMAT(CURDATE() , '%Y-%m-%d'))=year and month(DATE_FORMAT(CURDATE() , '%Y-%m-%d'))=month









-- GDP走势(旧) select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') as 'name','批发'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%批发%' and year(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') as 'name','批发'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%批发%' and year(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') as 'name','批发'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%批发%' and year(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') as 'name','批发'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%批发%' and year(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') as 'name','零售'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%零售%' and year(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') as 'name','零售'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%零售%' and year(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') as 'name','零售'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%零售%' and year(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') as 'name','零售'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%零售%' and year(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') as 'name','住宿'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%住宿%' and year(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') as 'name','住宿'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%住宿%' and year(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') as 'name','住宿'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%住宿%' and year(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') as 'name','住宿'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%住宿%' and year(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') as 'name','餐饮'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%餐饮%' and year(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') as 'name','餐饮'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%餐饮%' and year(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') as 'name','餐饮'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%餐饮%' and year(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m-%d'))=month union select ifnull(round(avg(month_gdp),2),0) as 'value',DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') as 'name','餐饮'as 'series' from hp_enterprise_gdp_month where belong_industrial like '%餐饮%' and year(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=year and month(DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m-%d'))=month






```


```/
select concat(h2.month,'月') as 'name',ifnull(normal_month_count,0) as 'value','更新数据量' as 'series' from hp_table_count_month h2 left join ( SELECT T.*,MONTH(date_value)+0 M FROM ( select normal_month_count,date_value from hp_table_count where year(date_value)=date_format(SYSDATE(),'%Y') ) T) t on t.m = h2.month order by h2.id

```