题解 | 每个商品的销售总额

select p.name product_name,sum(o.quantity) total_sales,RANK() OVER (PARTITION BY category ORDER BY sum(o.quantity) DESC) AS category_rank
from orders o
left join products p on o.product_id=p.product_id
group by p.product_id
having product_name is not null

1、组内排序

常用的窗口函数是 RANK()、DENSE_RANK() 和 ROW_NUMBER()，它们的区别如下：

窗口函数的功能

RANK()：按排序规则为每组的行排名，相同值会获得相同的排名，但排名会跳过（即存在排名间断）。

DENSE_RANK()：与RANK()类似，但排名不会跳过（即没有排名间断）。

ROW_NUMBER()：为每组的每行分配唯一的连续排名，即使值相同也不会重复。

语法sql复制代码

SELECT column1, column2, ...,

RANK() OVER (PARTITION BY group_column ORDER BY sort_column ASC) AS rank_column,

DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column ASC) AS dense_rank_column,

ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column ASC) AS row_number_columnFROM table_name;

示例假设有一个表 sales，包含以下字段：

region：区域employee：员工姓名sales_amount：销售额region employee sales_amount

East Alice 5000

East Bob 7000

East Charlie 7000

West David 8000

West Eve 6000

West Frank 8000

1. 按区域分组并对销售额排名 结果：

region employee sales_amount rank dense_rank row_number

East Bob 7000 1 1 1

East Charlie 7000 1 1 2

East Alice 5000 3 2 3

West David 8000 1 1 1

West Frank 8000 1 1 2

West Eve 6000 3 2 3

2. 自定义排序规则（使用CASE）可以结合 CASE 自定义排序规则。例如，优先按销售额排序，若销售额相同，再按员工姓名排序：

sql复制代码

SELECT region, employee, sales_amount,ROW_NUMBER() OVER (PARTITION BY regionORDER BY sales_amount DESC, employee ASC) AS row_number

FROM sales;

3. 将排名结果存储到新表如果需要将排名结果保存到新表：

CREATE TABLE ranked_sales AS

SELECT region, employee, sales_amount,RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS rankFROM sales;

窗口函数的优势灵活：可以轻松实现分组排名。性能：相比传统子查询方法，窗口函数性能更优。

2、排序前后不显示null

1. 使用 WHERE 子句直接过滤掉 NULL 值 在查询时排除 NULL 的记录，这样排序后的结果中就不会有 NULL：

SELECT column1, column2

FROM table_name

WHERE column1 IS NOT NULL

ORDER BY column1 ASC

示例数据：

column1 column2

100 A

NULL B

200 C

结果：

column1 column2

100 A

200 C

2. 使用 CASE 语句在排序时将 NULL 值移到最后如果不想直接排除 NULL，而是要排序时隐藏它们，可以用 CASE 语句：

SELECT column1, column2

FROM table_name

ORDER BY CASE WHEN column1 IS NULL THEN 1 ELSE 0 END, column1 ASC;

逻辑：CASE 把 NULL 的值标记为 1，非 NULL 标记为 0。排序时，NULL 的值被放到最后，其他值正常排序。

3. 使用 NULLS FIRST 或 NULLS LAST（特定数据库支持）某些数据库（如 PostgreSQL、Oracle）支持直接指定 NULL 的排序规则：

将 NULL 值放到最后：

SELECT column1, column2

FROM table_name

ORDER BY column1 ASC NULLS LAST;

将 NULL 值放到最前：

SELECT column1, column2

FROM table_name

ORDER BY column1 ASC NULLS FIRST;

注意：MySQL 不支持 NULLS LAST 或 NULLS FIRST，但可以用 CASE 来模拟。

4.排序后再过滤 有时你可能需要排序后只显示非 NULL 的结果。可以将查询嵌套，先排序再过滤：

SELECT column1, column2

FROM (

SELECT column1, column2

FROM table_name

ORDER BY column1 ASC) subquery

WHERE column1 IS NOT NULL;

选择方案的依据：

①数据中是否需要保留 NULL 行：如果不需要，直接用 WHERE 过滤。

如果需要保留，但在排序后隐藏，则用 CASE 或 NULLS LAST。

②数据库兼容性：PostgreSQL、Oracle 支持 NULLS LAST。MySQL 使用 CASE 处理更为通用。

补充：

用order by 的时候：

如果有NULL值：

• 默认升序时，NULL值排在最前面。
• 默认降序时，NULL值排在最后面。
• 可以使用NULLS FIRST或NULLS LAST明确指定