SQL中如何基于多列最大值获取表中对应列的数值

我有一张薪资表，相关字段定义如下：

• WageType 1：基本工资
• WageType 46：个人津贴

需要实现的逻辑：先找到基本工资（WageType=1）在所有月份列中的最大值，再获取该最大值对应的月份下个人津贴（WageType=46）的数值。以示例数据为例，基本工资最大值为37410，对应Nov、Dec两个月份，这两个月份对应的个人津贴数值都是0。

建表语句 #

CREATE TABLE [dbo].[Salary](
[PNO] [bigint] NULL,
[WageType] [int] NULL,
[Jul] [bigint] NULL,
[Aug] [bigint] NULL,
[Sep] [bigint] NULL,
[Oct] [bigint] NULL,
[Nov] [bigint] NULL,
[Dec] [bigint] NULL,
[Jan] [bigint] NULL,
[Feb] [bigint] NULL,
[Mar] [bigint] NULL,
[Apr] [bigint] NULL,
[May] [bigint] NULL,
[Jun] [bigint] NULL
) ON [PRIMARY]

测试数据插入语句 #

INSERT INTO [dbo].[Salary] ([PNO] ,[WageType] ,[Jul],[Aug] ,[Sep],[Oct],[Nov],[Dec],[Jan],[Feb],[Mar],[Apr],[May],[Jun]) 
VALUES (123, 1,33670,33670,36240,36240,37410,37410,0,0,0,0,0,0) 

INSERT INTO [dbo].[Salary] ([PNO] ,[WageType] ,[Jul],[Aug] ,[Sep],[Oct],[Nov],[Dec],[Jan],[Feb],[Mar],[Apr],[May],[Jun]) 
VALUES (123, 46,730,730,0,0,0,0,0,0,0,0,0,0)

因为表是宽表结构，月份都存储为列，所以需要先做行转列把月份拆为行数据，再匹配对应取值，以下是适配SQL Server的写法：

WITH UnpivotSalary AS (
    -- 行转列，把月份列转为行格式
    SELECT 
        PNO,
        WageType,
        MonthName,
        SalaryValue
    FROM [dbo].[Salary]
    UNPIVOT (
        SalaryValue FOR MonthName IN (Jul,Aug,Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun)
    ) AS unpvt
),
MaxBasicMonth AS (
    -- 筛选出基本工资最高的所有月份
    SELECT TOP 1 WITH TIES 
        PNO,
        MonthName
    FROM UnpivotSalary
    WHERE WageType = 1
    ORDER BY SalaryValue DESC
)
-- 关联获取对应月份的个人津贴数值
SELECT 
    m.PNO,
    m.MonthName AS 最高基本工资月份,
    s1.SalaryValue AS 最高基本工资,
    s2.SalaryValue AS 对应月份个人津贴
FROM MaxBasicMonth m
JOIN UnpivotSalary s1 ON m.PNO = s1.PNO AND m.MonthName = s1.MonthName AND s1.WageType =1
JOIN UnpivotSalary s2 ON m.PNO = s2.PNO AND m.MonthName = s2.MonthName AND s2.WageType =46

如果不需要返回所有最高基本工资月份的结果，只需要任意一个，去掉TOP 1 WITH TIES即可。

如果使用的是不支持UNPIVOT语法的数据库（比如MySQL），可以用UNION ALL手动实现行转列：

WITH UnpivotSalary AS (
    SELECT PNO, WageType, 'Jul' as MonthName, Jul as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Aug' as MonthName, Aug as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Sep' as MonthName, Sep as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Oct' as MonthName, Oct as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Nov' as MonthName, Nov as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Dec' as MonthName, Dec as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Jan' as MonthName, Jan as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Feb' as MonthName, Feb as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Mar' as MonthName, Mar as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Apr' as MonthName, Apr as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'May' as MonthName, May as SalaryValue FROM Salary UNION ALL
    SELECT PNO, WageType, 'Jun' as MonthName, Jun as SalaryValue FROM Salary
)
-- 后续筛选逻辑和上面SQL Server版本完全一致

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