如何生成含分州薪资与平均薪资的目标SQL结果表？

需求说明 #

针对position列中的每个职位类别，计算minrate和maxrate的平均值，同时将不同state对应的minrate和maxrate以列的形式展示（当前为样本数据集，实际存在更多记录）。

样本数据集 #

state   position        minrate  maxrate
ny      admin assistant 12.5000  14.5000
ny      office manager  20.5000  25.5000
ca      admin assistant 13.5000  15.5000
ca      office manager  21.5000  26.5000
al      admin assistant 11.5000  13.5000
al      office manager  19.5000  24.5000

预期结果 #

position        ny_min   ny_max   ca_min   ca_max   al_min   al_max   avg_min  avg_max
admin assistant 12.5000  14.5000  13.5000  15.5000  11.5000  13.5000  12.5000  14.5000
office manager  20.5000  25.5000  21.5000  26.5000  19.5000  24.5000  20.5000  25.5000

样本数据初始化SQL #

declare @jobs table (
[state] nvarchar(25),
[position] nvarchar(25),
[minrate] decimal(18,4),
[maxrate] decimal(18,4)
)

insert @jobs
values
('ny','admin assistant',12.5, 14.5),
('ny','office manager',20.5, 25.5),
('ca','admin assistant',13.5, 15.5),
('ca','office manager',21.5, 26.5),
('al','admin assistant',11.5, 13.5),
('al','office manager',19.5, 24.5)

解决方案SQL #

1. 静态列转行（适配已知州的情况） #

如果明确需要展示的州列表，可直接用CASE语句实现：

SELECT
    position,
    MAX(CASE WHEN state = 'ny' THEN minrate END) AS ny_min,
    MAX(CASE WHEN state = 'ny' THEN maxrate END) AS ny_max,
    MAX(CASE WHEN state = 'ca' THEN minrate END) AS ca_min,
    MAX(CASE WHEN state = 'ca' THEN maxrate END) AS ca_max,
    MAX(CASE WHEN state = 'al' THEN minrate END) AS al_min,
    MAX(CASE WHEN state = 'al' THEN maxrate END) AS al_max,
    AVG(minrate) AS avg_min,
    AVG(maxrate) AS avg_max
FROM @jobs
GROUP BY position
ORDER BY position

2. 动态列转行（适配未知州的情况） #

如果实际数据包含未知数量的州，可通过动态SQL自动生成列：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)

-- 自动生成所有州对应的min/max列定义
SELECT @cols = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN state = ''', state, ''' THEN minrate END) AS ', QUOTENAME(state + '_min'), ',',
        'MAX(CASE WHEN state = ''', state, ''' THEN maxrate END) AS ', QUOTENAME(state + '_max')
    ),
    ','
)
FROM (SELECT DISTINCT state FROM @jobs) AS states

-- 拼接完整查询语句
SET @query = CONCAT(
    'SELECT position,', @cols, ',',
    'AVG(minrate) AS avg_min,',
    'AVG(maxrate) AS avg_max',
    ' FROM @jobs GROUP BY position ORDER BY position'
)

-- 执行动态SQL
EXEC sp_executesql @query, N'@jobs TABLE ([state] nvarchar(25), [position] nvarchar(25), [minrate] decimal(18,4), [maxrate] decimal(18,4))', @jobs = @jobs

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