【mySql时间戳学习】月每天，年每月（没有做0处理），一星期每天（没有做0处理）

/*总会员数*/ SELECT COUNT(`username`) FROM ums_member /*出售商品数*/ FROM oms_order SELECT COUNT(order_sn) /*查看当日订单数*/ SELECT COUNT(id) FROM oms_order WHERE TO_DAYS(create_time) = TO_DAYS(NOW()); /*查询当日销售额*/ SELECT SUM(total_amount) FROM oms_order WHERE TO_DAYS(create_time) = TO_DAYS(NOW()); /*查看当月的销售额*/ SELECT SUM(total_amount) FROM oms_order WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) =DATE_FORMAT(NOW(),'%Y-%m-%d'); /*查询上个月的销售额度*/ SELECT SUM(total_amount) FROM oms_order WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') /*查当月的数据*/ SELECT SUM(total_amount) FROM oms_order WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m') SELECT SUM(total_amount) FROM oms_order WHERE DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) /*查询近一年的数据*/ SELECT SUM(total_amount) FROM oms_order WHERE YEAR(create_time)=YEAR(NOW()); /*查看当前月的数据*/ SELECT i.product_brand,i.product_name,i.product_quantity FROM `oms_order` o LEFT JOIN `oms_order_item` i ON o.id=i.order_id WHERE YEARWEEK(DATE_FORMAT(o.create_time,'%Y-%m-%d')) =DATE_FORMAT(NOW(),'%Y-%m-%d') ORDER BY i.product_quantity DESC /*查询上个月的数据*/ SELECT i.product_brand,i.product_name,i.product_quantity FROM `oms_order` o LEFT JOIN `oms_order_item` i ON o.id=i.order_id WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') ORDER BY i.product_quantity DESC LIMIT 1,10 /*前5条记录*/ /*查看一年的*/ SELECT i.product_brand,i.product_name,i.product_quantity FROM `oms_order_item` i LEFT JOIN `oms_order` o ON o.id=i.order_id WHERE YEAR(o.create_time)=YEAR(NOW()) ORDER BY i.product_quantity DESC LIMIT 1,10 /*查询总的数据*/ SELECT product_brand ,product_name ,product_quantity FROM oms_order_item ORDER BY product_quantity DESC LIMIT 1,10

年每月销售额度

/*年每月销售额度*/ SELECT IFNULL(SUM(CASE MONTH(create_time) WHEN '1' THEN total_amount ELSE 0 END), 0) AS 一月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '2' THEN total_amount ELSE 0 END), 0) AS 二月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '3' THEN total_amount ELSE 0 END), 0) AS 三月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '4' THEN total_amount ELSE 0 END), 0) AS 四月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '5' THEN total_amount ELSE 0 END), 0) AS 五月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '6' THEN total_amount ELSE 0 END), 0) AS 六月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '7' THEN total_amount ELSE 0 END), 0) AS 七月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '8' THEN total_amount ELSE 0 END), 0) AS 八月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '9' THEN total_amount ELSE 0 END), 0) AS 九月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '10' THEN total_amount ELSE 0 END), 0) AS 十月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '11' THEN total_amount ELSE 0 END), 0) AS 十一月份, IFNULL(SUM(CASE MONTH(create_time) WHEN '12' THEN total_amount ELSE 0 END), 0) AS 十二月份 FROM `oms_order` WHERE YEAR(create_time)=YEAR(NOW())

/*一周查询没有的做0处理*/ SELECT a.thisweek,IFNULL(b.count,0) AS DATE FROM ( SELECT DATE(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1)) AS thisweek UNION ALL SELECT DATE(DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), INTERVAL 1 DAY)) AS thisweek UNION ALL SELECT DATE(DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), INTERVAL 2 DAY)) AS thisweek UNION ALL SELECT DATE(DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), INTERVAL 3 DAY)) AS thisweek UNION ALL SELECT DATE(DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), INTERVAL 4 DAY)) AS thisweek UNION ALL SELECT DATE(DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), INTERVAL 5 DAY)) AS thisweek UNION ALL SELECT DATE(DATE_ADD(SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), INTERVAL 6 DAY)) AS thisweek ) a LEFT JOIN ( SELECT DATE(create_time) AS DATETIME, COUNT(*) AS COUNT FROM oms_order GROUP BY DATE(create_time) ) b ON a.thisweek = b.datetime;

/*年每月的销售额*/ SELECT SUM(a.total_amount) AS total_amount, DATE_FORMAT(a.create_time,'%Y-%m') AS 'data' FROM `oms_order` a WHERE YEAR(a.create_time)=YEAR(NOW()) GROUP BY DATA /*月每天销售额*/ SELECT SUM(total_amount) AS total_amount,DATE_FORMAT(create_time,'%Y-%m-%d')AS `date` FROM oms_order WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m') GROUP BY DATE /*本季度每月销售额*/ SELECT SUM(total_amount) AS total_amount,DATE_FORMAT(create_time,'%Y-%m')AS `date` FROM `oms_order` WHERE QUARTER(create_time)=QUARTER(NOW()) GROUP BY DATE /*查询周每天销售额*/ SELECT SUM(total_amount) AS total_amount ,DATE_FORMAT(create_time,'%Y-%m-%d-%W') AS DATE FROM oms_order WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) GROUP BY DATE /*查询本季度每月销售额*/ SELECT SUM(total_amount) AS total_amount,DATE_FORMAT(create_time,'%Y-%m')AS DATE FROM `oms_order` WHERE QUARTER(create_time)=QUARTER(NOW()) GROUP BY DATE; /*去年每月销售额*/ SELECT SUM(total_amount) AS total_amount, DATE_FORMAT(a.create_time,'%Y-%m') AS 'date' FROM `oms_order` a WHERE YEAR(a.create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) GROUP BY DATE /*查询本季度每月的订单数据*/ SELECT COUNT(*) AS orderNumber,DATE_FORMAT(create_time,'%Y-%m')AS times FROM `oms_order` WHERE QUARTER(create_time)=QUARTER(NOW()) GROUP BY times; /*查询上个季度每个月订单数据*/ SELECT COUNT(*),DATE_FORMAT(create_time,'%M')AS times FROM `oms_order` WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) GROUP BY times; /*月每天订单数据*/ SELECT COUNT(*) AS orderNumber,DATE_FORMAT(create_time,'%Y-%m-%d')AS `date` FROM oms_order WHERE DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m') GROUP BY DATE /*当前星期每天订单数*/ SELECT COUNT(id) AS orderNumber ,DATE_FORMAT(create_time,'%Y-%m-%d-%W') AS DATE FROM oms_order WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1 GROUP BY DATE /*年每月订单数*/ SELECT COUNT(*) AS orderNumber, DATE_FORMAT(a.create_time,'%Y-%m') AS 'data' FROM `oms_order` a WHERE YEAR(a.create_time)=YEAR(NOW()) GROUP BY DATA /*去年每月订单数*/ SELECT COUNT(*) AS orderNumber, DATE_FORMAT(a.create_time,'%Y-%m') AS 'data' FROM `oms_order` a WHERE YEAR(a.create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) GROUP BY DATA