一个网站费用,住房建设建设部网站,正规的企业网站建设,wordpress散开式有时候#xff0c;我们可能有这样的场景#xff0c;需要将销量按月统计#xff0c;并且按月逐月累加。写惯了GROUP BY,按月统计倒是小case,但是逐月累加实现起来#xff0c;要稍微麻烦一点。下面就整理几种写法#xff0c;以备不时之需。
本月第一天
-- 本月第一天
SELE…有时候我们可能有这样的场景需要将销量按月统计并且按月逐月累加。写惯了GROUP BY,按月统计倒是小case,但是逐月累加实现起来要稍微麻烦一点。下面就整理几种写法以备不时之需。
本月第一天
-- 本月第一天
SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) 1 DAY);-- 本月第一天
SELECT CONCAT(DATE_FORMAT(CURDATE(), %Y-%m), -01); 建表及模拟数据
-- 创建表
CREATE TABLE sales (id int(11) NOT NULL AUTO_INCREMENT,sales_date date NOT NULL,sales_amount decimal(10,2) NOT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;-- 插入模拟数据
INSERT INTO sales (sales_date, sales_amount) VALUES
(2023-01-01, 1500.00),
(2023-01-02, 1800.00),
(2023-01-05, 2200.00),
(2023-02-01, 1200.00),
(2023-02-03, 1800.00),
(2023-03-01, 2500.00),
(2023-03-05, 2800.00),
(2023-03-08, 3200.00),
(2023-04-01, 2100.00),
(2023-04-03, 1900.00),
(2023-04-05, 2600.00),
(2023-05-01, 3100.00),
(2023-05-02, 3400.00),
(2023-06-01, 3800.00),
(2023-06-06, 4200.00); 该表包含三个字段id、sales_date、sales_amount。id为自增长主键sales_date为销售日期sales_amount为销售额。插入了15条模拟数据涵盖了2023年1月至6月的销售数据 一、自连接和子查询
首先在内部查询中计算出每个月份的销售总额和月份接着在外部查询中使用自连接和子查询计算每个月份的累计销售额 SELECT t1.month, t1.monthly_sales,SUM(t2.monthly_sales) AS cumulative_sales
FROM (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month,SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month
) t1
JOIN (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month,SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month
) t2 ON t1.month t2.month
GROUP BY t1.month; 二、子查询
SELECT month, monthly_sales,(SELECT SUM(monthly_sales) FROM (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month, SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month) t2WHERE t2.month t1.month) AS cumulative_sales
FROM (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month, SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month
) t1; 此处使用了两个子查询第一个子查询用于获取每个月份的总销售额和月份第二个子查询用于计算累加值。在内部子查询中通过操作符将当前月份以及之前所有月份的销售额相加从而得到累加值 三、子查询变量 SELECT month, monthly_sales, cumulative : cumulative monthly_sales AS cumulative
FROM (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month, SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month
) t1
CROSS JOIN (SELECT cumulative : 0) t2; 此处使用了两个子查询第一个子查询用于获取每个月份的总销售额和月份第二个子查询用于初始化变量cumulative。在外部查询中通过CROSS JOIN将两个子查询连接起来并且使用变量cumulative来计算累加值。 四、用户变量和子查询
在内部查询中先对销售日期进行排序然后使用用户变量cumulative来记录每个月份的累加值。在最终的查询结果中输出月份、当月销售额以及累加值
SELECT month, monthly_sales,(cumulative : cumulative monthly_sales) AS cumulative_sales
FROM (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month, SUM(sales_amount) AS monthly_salesFROM salesGROUP BY monthORDER BY sales_date ASC
) t1, (SELECT cumulative : 0) t2; 此处使用了两个子查询第一个子查询用于获取每个月份的总销售额和月份并按销售日期升序排序第二个子查询用于初始化用户变量cumulative。在外部查询中通过,连接两个子查询并使用用户变量cumulative来计算每个月份的累加值。 五、表达式CTE和窗口函数
使用MySQL 8.0引入的通用表表达式CTE和窗口函数可以将累加值计算放在CTE中完成 WITH monthly_sales AS (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month, SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month
)
SELECT month, monthly_sales,SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM monthly_sales; 此处使用了CTE来计算每个月份的总销售额和月份并在外部查询中使用窗口函数SUM() OVER()对月份进行累加。 SUM() OVER()
使用MySQL 8.0引入的LATERAL关键字以及OVER ORDER BY子句按月份求和再用SUM() OVER()进行累加并分别输出月份、当月销售金额和累计销售金额 ##月统计
SELECT month, monthly_sales,SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales
FROM (SELECT DATE_FORMAT(sales_date, %Y-%m) AS month,SUM(sales_amount) AS monthly_salesFROM salesGROUP BY month
) t1;##日统计
SELECT day, monthly_sales,SUM(monthly_sales) OVER (ORDER BY day) AS cumulative_sales
FROM (SELECT DATE_FORMAT(sales_date, %Y-%m-%d) AS day,SUM(sales_amount) AS monthly_salesFROM salesGROUP BY day
) t1;
