上月上季度上年

SELECT '上月平均' title,
                    IFNULL(Round(
                        sum(DATEDIFF(END, START)) / (
                            SELECT
                                count(*)
                            FROM
                                t_dws_xzfy_fy
                            WHERE
                                 PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( start, '%Y%m' ) ) =1
                        ),
                        2
                    ),0 )AS `value`,'天/件' valueUnit
                FROM
                    t_dws_xzfy_fy
                WHERE
                    PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( start, '%Y%m' ) ) =1
        UNION
        SELECT '上季平均' title,
                    IFNULL(Round(
                        sum(DATEDIFF(END, START)) / (
                            SELECT
                                count(*)
                            FROM
                                t_dws_xzfy_fy
                            WHERE
                                 QUARTER(start)=QUARTER(DATE_SUB(now(),interval 1 QUARTER))
                        ),
                        2
                    ),0 )AS `value`,'天/件' valueUnit
                FROM
                    t_dws_xzfy_fy
                WHERE
                    QUARTER(start)=QUARTER(DATE_SUB(now(),interval 1 QUARTER))
        UNION
        SELECT '上年平均' title,
            IFNULL(Round(
                sum(DATEDIFF(END, START)) / (
                    SELECT
                        count(*)
                    FROM
                        t_dws_xzfy_fy
                    WHERE
                          year(start)=year(date_sub(now(),interval 1 year))
                ),
                2
            ),0 )AS `value`,'天/件' valueUnit
        FROM
            t_dws_xzfy_fy
        WHERE
            year(start)=year(date_sub(now(),interval 1 year))

 

SELECT '上月平均' title,
            IFNULL(Round(
                sum(DATEDIFF(END, START)) / (
                    SELECT
                        count(*)
                    FROM
                        t_dws_xzfy_fy
                    WHERE
                         month(start)=month(DATE_SUB(now(),interval 1 month)) and (year(start)=year(now()) or ((month(now())<=1) and year(start)=year(now())-1))
                ),
                2
            ),0 )AS `value`,'天/件' valueUnit
        FROM
            t_dws_xzfy_fy
        WHERE
            month(start)=month(DATE_SUB(now(),interval 1 month)) and (year(start)=year(now()) or ((month(now())<=1) and year(start)=year(now())-1))
UNION
SELECT '上季平均' title,
            IFNULL(Round(
                sum(DATEDIFF(END, START)) / (
                    SELECT
                        count(*)
                    FROM
                        t_dws_xzfy_fy
                    WHERE
                         QUARTER(start)=QUARTER(DATE_SUB(now(),interval 1 QUARTER)) and  
             (year(start)=year(now()) or ((year(start)=year(now())-1) and month(now())<=3))
                ),
                2
            ),0 )AS `value`,'天/件' valueUnit
        FROM
            t_dws_xzfy_fy
        WHERE
            QUARTER(start)=QUARTER(DATE_SUB(now(),interval 1 QUARTER))and 
       (year(start)=year(now()) or ((year(start)=year(now())-1) and month(now())<=3))
UNION
SELECT '上年平均' title,
    IFNULL(Round(
        sum(DATEDIFF(END, START)) / (
            SELECT
                count(*)
            FROM
                t_dws_xzfy_fy
            WHERE
                  year(start)=year(date_sub(now(),interval 1 year))
        ),
        2
    ),0 )AS `value`,'天/件' valueUnit
FROM
    t_dws_xzfy_fy
WHERE
    year(start)=year(date_sub(now(),interval 1 year))