sql按照年、月、周、日查看数据

<!--首页交易金额统计  -->
<!--按照年查看 -->
    <select id="queryStatByYear"  resultMap="StatResultMap" parameterType="Object">
        SELECT temp1.yeardate AS yearofdate,TRUNCATE(SUM(temp1.price + temp2.moneySum),2) AS statprice FROM 
            (SELECT YEAR(o.user_confirm_time) AS yeardate,SUM(o.pre_amount * o.num) AS price 
                FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL GROUP BY YEAR(o.user_confirm_time) 
                ORDER BY o.user_confirm_time DESC) AS temp1,
            (SELECT  YEAR(u.change_time) AS yeartime,SUM(u.change_money) AS moneySum 
                FROM  users_balance_history u WHERE u.shopid IS NOT NULL GROUP BY YEAR(u.change_time)
                ORDER BY u.change_time DESC) AS temp2
        WHERE temp1.yeardate = temp2.yeartime
    </select>
    <!--按照月查看  -->
    <select id="queryStatByMonth"  resultMap="StatResultMap" parameterType="Object">
        SELECT temp1.yeardate AS yearofdate,temp1.monthdate AS monthofdate, TRUNCATE(SUM(temp1.price),2) AS statprice FROM 
            ((SELECT YEAR(o.user_confirm_time) AS yeardate,
                MONTH(o.user_confirm_time) AS monthdate,        
                SUM(o.pre_amount * o.num) AS price 
                FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL GROUP BY
                YEAR(o.user_confirm_time),
                MONTH(o.user_confirm_time)ORDER BY o.user_confirm_time DESC)
            UNION 
            (SELECT YEAR(u.change_time) AS yearstime,
                MONTH(u.change_time) AS monthsdate,
                SUM(u.change_money) AS moneySum
                FROM  users_balance_history u WHERE u.shopid IS NOT NULL GROUP BY 
                YEAR(u.change_time),
                MONTH(u.change_time)
                ORDER BY u.change_time DESC) ) AS temp1
            GROUP  BY temp1.monthdate ORDER BY temp1.monthdate DESC
    </select>
    <!-- 按照周查看 -->
    <select id="queryStatByWeek"  resultMap="StatResultMap" parameterType="Object">
        SELECT temp1.yeardate AS yearofdate,temp1.monthdate AS monthofdate,temp1.weekdate AS weekofdate, TRUNCATE(SUM(temp1.price),2) AS statprice FROM 
            ((SELECT YEAR(o.user_confirm_time) AS yeardate,
                MONTH(o.user_confirm_time) AS monthdate,
                WEEK(o.user_confirm_time) AS weekdate,
                SUM(o.pre_amount * o.num) AS price 
                FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL GROUP BY
                YEAR(o.user_confirm_time),
                MONTH(o.user_confirm_time),
                WEEK(o.user_confirm_time)  ORDER BY o.user_confirm_time DESC)
             UNION    
            (SELECT YEAR(u.change_time) AS yearstime,
                MONTH(u.change_time) AS monthsdate,
                WEEK(u.change_time) AS weeksdate,
                SUM(u.change_money) AS moneySum 
                FROM  users_balance_history u WHERE u.shopid IS NOT NULL GROUP BY 
                YEAR(u.change_time),
                MONTH(u.change_time),
                WEEK(u.change_time)
                ORDER BY u.change_time DESC)) AS temp1
          GROUP  BY temp1.weekdate ORDER BY temp1.weekdate DESC
    </select>
    <!-- 按照天查看 -->
    <select id="queryStatByDay"  resultMap="StatResultMap" parameterType="Object">
         SELECT temp1.yeardate AS yearofdate,temp1.monthdate AS monthofdate,
            temp1.daydate AS dayofdate, TRUNCATE(SUM(temp1.price),2) AS statprice FROM 
            ((SELECT YEAR(o.user_confirm_time) AS yeardate,
                MONTH(o.user_confirm_time) AS monthdate,
                DAY(o.user_confirm_time) AS daydate,        
                SUM(o.pre_amount * o.num) AS price 
                FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL GROUP BY
                YEAR(o.user_confirm_time),
                MONTH(o.user_confirm_time),
                DAY(o.user_confirm_time) ORDER BY o.user_confirm_time DESC)
            UNION 
            (SELECT YEAR(u.change_time) AS yearstime,
                MONTH(u.change_time) AS monthsdate,
                DAY(u.change_time) AS daysdate,
                SUM(u.change_money) AS moneySum
                FROM  users_balance_history u WHERE u.shopid IS NOT NULL GROUP BY 
                YEAR(u.change_time),
                MONTH(u.change_time),
                DAY(u.change_time)
                ORDER BY u.change_time DESC) ) AS temp1
            GROUP  BY temp1.monthdate,temp1.daydate ORDER BY temp1.monthdate DESC,temp1.daydate DESC
    </select>


<!-- 首页订单数统计 -->
<!-- 按照年统计 -->
    <select id="queryStatByYear2"  resultMap="Stat2ResultMap" parameterType="Object">
            SELECT temp1.yeardate AS yearofdate, COUNT(*) AS `count` FROM 
                ((SELECT YEAR(o.user_confirm_time) AS yeardate
                    FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL AND o.status=5
                    ORDER BY o.user_confirm_time DESC)
                UNION  ALL
                (SELECT YEAR(u.change_time) AS yearstime
                    FROM  users_balance_history u WHERE u.shopid IS NOT NULL AND u.is_success='1'
                    ORDER BY u.change_time DESC) ) AS temp1
                GROUP  BY temp1.yeardate ORDER BY 
                temp1.yeardate DESC    
    </select>
<!-- 按照月统计 -->
    <select id="queryStatByMonth2"  resultMap="Stat2ResultMap" parameterType="Object">
        <!-- select year(`order`.user_confirm_time) as 'yearofdate', MONTH(`order`.user_confirm_time) as 'monthofdate',  count(1) as 'count'
        from `order` where `order`.user_confirm_time IS NOT NULL  group by year(`order`.user_confirm_time),MONTH(`order`.user_confirm_time) ORDER BY `order`.user_confirm_time DESC limit 0,10 -->
        SELECT temp1.yeardate AS yearofdate,temp1.monthdate AS monthofdate, COUNT(*) AS `count` FROM 
        ((SELECT YEAR(o.user_confirm_time) AS yeardate,
            MONTH(o.user_confirm_time) AS monthdate
            FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL AND o.status=5
            ORDER BY o.user_confirm_time DESC)
        UNION  ALL
        (SELECT YEAR(u.change_time) AS yearstime,
            MONTH(u.change_time) AS monthdate
            FROM  users_balance_history u WHERE u.shopid IS NOT NULL AND u.is_success='1'
            ORDER BY u.change_time DESC) ) AS temp1
        GROUP  BY temp1.monthdate ORDER BY 
        temp1.monthdate DESC    
    </select>
<!-- 按照周统计 -->
    <select id="queryStatByWeek2"  resultMap="Stat2ResultMap" parameterType="Object">
        <!-- select year(`order`.user_confirm_time) as 'yearofdate', MONTH(`order`.user_confirm_time) as 'monthofdate',week(`order`.user_confirm_time) as 'weekofdate',count(1) as 'count'
        from `order` where `order`.user_confirm_time IS NOT NULL  group by year(`order`.user_confirm_time),MONTH(`order`.user_confirm_time),week(`order`.user_confirm_time) ORDER BY `order`.user_confirm_time DESC limit 0,10 -->
            SELECT temp1.yeardate AS yearofdate,temp1.weekdate AS weekofdate, COUNT(*) AS `count` FROM 
        ((SELECT YEAR(o.user_confirm_time) AS yeardate,
            WEEK(o.user_confirm_time) AS weekdate
            FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL AND o.status=5
            ORDER BY o.user_confirm_time DESC)
        UNION  ALL
        (SELECT YEAR(u.change_time) AS yearstime,
            WEEK(u.change_time) AS weekdate
            FROM  users_balance_history u WHERE u.shopid IS NOT NULL AND u.is_success='1'
            ORDER BY u.change_time DESC) ) AS temp1
        GROUP  BY temp1.weekdate ORDER BY 
        temp1.weekdate DESC      
    </select>
<!-- 按照天统计 -->
    <select id="queryStatByDay2"  resultMap="Stat2ResultMap" parameterType="Object">
        SELECT temp1.yeardate AS yearofdate,temp1.monthdate AS monthofdate,
        temp1.daydate AS dayofdate, COUNT(*) AS `count` FROM 
        ((SELECT YEAR(o.user_confirm_time) AS yeardate,
            MONTH(o.user_confirm_time) AS monthdate,
            DAY(o.user_confirm_time) AS daydate
            FROM `order_goods` o WHERE o.user_confirm_time IS NOT NULL AND o.status=5
            ORDER BY o.user_confirm_time DESC)
        UNION  ALL
        (SELECT YEAR(u.change_time) AS yearstime,
            MONTH(u.change_time) AS monthsdate,
            DAY(u.change_time) AS daysdate
            FROM  users_balance_history u WHERE u.shopid IS NOT NULL AND u.is_success='1'
            ORDER BY u.change_time DESC) ) AS temp1
        GROUP  BY temp1.monthdate,temp1.daydate ORDER BY 
        temp1.monthdate DESC,temp1.daydate DESC
    </select>