MYSQL 常用统计SQL

最新推荐文章于 2023-08-20 12:42:05 发布

sunmking

最新推荐文章于 2023-08-20 12:42:05 发布

阅读量258

点赞数

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/sinat_16631869/article/details/127425811

版权

统计当年每月的出库数量，没有的月份补0

								SELECT
                    IFNULL( b.num, 0 ) AS quantity,
                    a.date 
                FROM
                    (
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-01" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-02" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-03" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-04" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-05" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-06" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-07" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-08" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-09" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-10" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-11" ) AS date UNION
                    SELECT
                        CONCAT( DATE_FORMAT( NOW(), "%Y" ), "-12" ) AS date 
                    ) a
                    LEFT JOIN (
                    SELECT
                        sum( dod.quantity ) AS num,
                        FROM_UNIXTIME( tdo.delivery_time, "%Y-%m" ) AS date_x 
                    FROM
                        tj_delivery_order AS tdo
                        LEFT JOIN tj_delivery_order_detail AS dod ON tdo.id = dod.order_id 
                    WHERE
                        tdo.STATUS = 4 
                    GROUP BY
                        date_x 
                    ORDER BY
                    date_x 
                    ) AS b ON a.date = b.date_x

统计最近几天的出库数量，没有的补0

								SELECT
                    a.click_date,
                    IFNULL( b.num, 0 ) quantity 
                FROM
                    (
                    SELECT
                        curdate( ) AS click_date UNION ALL
                    SELECT
                        date_sub( curdate( ), INTERVAL 1 DAY ) AS click_date UNION ALL
                    SELECT
                        date_sub( curdate( ), INTERVAL 2 DAY ) AS click_date UNION ALL
                    SELECT
                        date_sub( curdate( ), INTERVAL 3 DAY ) AS click_date UNION ALL
                    SELECT
                        date_sub( curdate( ), INTERVAL 4 DAY ) AS click_date UNION ALL
                    SELECT
                        date_sub( curdate( ), INTERVAL 5 DAY ) AS click_date UNION ALL
                    SELECT
                        date_sub( curdate( ), INTERVAL 6 DAY ) AS click_date 
                    ) a
                    LEFT JOIN (
                    SELECT
                        sum( dod.quantity ) AS num,
                        FROM_UNIXTIME(tdo.delivery_time,"%Y-%m-%d") AS date_x 
                    FROM
                        tj_delivery_order AS tdo
                        LEFT JOIN tj_delivery_order_detail AS dod ON tdo.id = dod.order_id 
                    WHERE
                        tdo.STATUS = 4 
                    GROUP BY
                        date_x 
                    ORDER BY
                    date_x 
                    ) AS b ON a.click_date = b.date_x ORDER BY click_date ASC