sql语句实现多次分组
时间: 2023-11-21 16:23:13 浏览: 147
可以使用嵌套子查询或者使用WITH语句来实现多次分组。
1. 嵌套子查询
例如,有一个表格sales,其中包含销售数据(日期、销售量、销售额、销售员),我们需要按照销售员、月份对销售数据进行汇总。
首先,我们可以使用子查询按照月份对销售数据进行汇总,然后再在外层查询中按照销售员进行汇总。
SELECT salesperson, SUM(monthly_sales) AS total_sales
FROM (
SELECT salesperson, MONTH(sales_date) AS month, SUM(sales_amount) AS monthly_sales
FROM sales
GROUP BY salesperson, MONTH(sales_date)
) AS monthly_sales_by_salesperson
GROUP BY salesperson;
2. WITH语句
使用WITH语句可以将多次分组的子查询放在一个语句中,使查询更加清晰。
例如,我们需要按照销售员、月份、年份对销售数据进行汇总。
WITH monthly_sales_by_salesperson AS (
SELECT salesperson, MONTH(sales_date) AS month, YEAR(sales_date) AS year, SUM(sales_amount) AS monthly_sales
FROM sales
GROUP BY salesperson, MONTH(sales_date), YEAR(sales_date)
), yearly_sales_by_salesperson AS (
SELECT salesperson, year, SUM(monthly_sales) AS yearly_sales
FROM monthly_sales_by_salesperson
GROUP BY salesperson, year
)
SELECT salesperson, year, SUM(yearly_sales) AS total_sales
FROM yearly_sales_by_salesperson
GROUP BY salesperson, year;
阅读全文