如何用SQL筛选表格并按ID排序，生成区域内相对序号？

原始数据表格 #

需求说明 #

筛选指定Area（示例为B），按ID升序排序后，生成包含Area、区域内相对序号（Order）、Value的结果表格。

1. SQL 实现方案 #

如果数据存储在数据库中，使用窗口函数ROW_NUMBER()可以高效完成需求：

SELECT 
    Area,
    ROW_NUMBER() OVER (PARTITION BY Area ORDER BY ID ASC) AS `Order`,
    Value
FROM 
    your_table_name
WHERE 
    Area = 'B'
ORDER BY 
    ID ASC;

• PARTITION BY Area：按区域分组，每组单独计算序号
• ORDER BY ID ASC：组内按ID升序排列，生成连续序号
• 替换Area = 'B'即可筛选其他区域

2. Python Pandas 实现方案 #

处理本地表格数据时，用Pandas的分组和计数功能实现：

import pandas as pd

# 构造原始数据
data = {
    'Area': ['A', 'A', 'B', 'B', 'C', 'B', 'A'],
    'ID': [1, 2, 3, 4, 5, 6, 7],
    'Value': [0.5, 1.2, 1.1, 0.8, 1.0, 0.6, 1.5]
}
df = pd.DataFrame(data)

# 筛选+排序+生成序号
filtered_df = df[df['Area'] == 'B'].sort_values('ID')
filtered_df['Order'] = filtered_df.groupby('Area').cumcount() + 1  # 从1开始计数

# 整理结果
result = filtered_df[['Area', 'Order', 'Value']].reset_index(drop=True)
print(result)

运行结果：

Area  Order  Value
0    B      1    1.1
1    B      2    0.8
2    B      3    0.6

3. Excel 实现方案 #

在Excel中通过筛选和函数完成：

1. 选中表格，点击「数据」→「筛选」，在Area列下拉选择B
2. 在空白列（如D列）第一个可见单元格输入公式：=SUBTOTAL(3,$B$2:B2)
3. 下拉填充公式，SUBTOTAL(3,...)会自动忽略隐藏行，生成连续的相对序号
4. 整理Area、Order、Value三列即可得到目标表格

内容的提问来源于stack exchange，提问作者enstit