sql-server - 如何根据SQL中列之间的最大值从表中获取对应的列值

Question

我有一张包含以下信息的表格。

• 工资类型 1：基本工资
• 工资类型 46：个人工资

我想要基于此最大值的 Months 列中的 Basic Pay Max 即 37410（Jan 列）我想要对应的 Personal Pay Value 为 0。我想要 SQL 查询以获取以下结果

对上表使用以下创建和插入。

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

Answer 1

这是一个非常糟糕的设计，例如，我看不出你将如何存储超过一年的工资。CROSS APPLY话虽如此，您可以使用然后执行一些聚合来规范化数据：

WITH cte1 AS (
    SELECT pno, mon, MAX(wage1) AS wage1, MAX(wage46) AS wage46
    FROM t
    CROSS APPLY (VALUES
        (1,  CASE WHEN wagetype = 1 THEN Jan END, CASE WHEN wagetype = 46 THEN Jan END),
        (2,  CASE WHEN wagetype = 1 THEN Feb END, CASE WHEN wagetype = 46 THEN Feb END),
        (3,  CASE WHEN wagetype = 1 THEN Mar END, CASE WHEN wagetype = 46 THEN Mar END),
        (4,  CASE WHEN wagetype = 1 THEN Apr END, CASE WHEN wagetype = 46 THEN Apr END),
        (5,  CASE WHEN wagetype = 1 THEN May END, CASE WHEN wagetype = 46 THEN May END),
        (6,  CASE WHEN wagetype = 1 THEN Jun END, CASE WHEN wagetype = 46 THEN Jun END),
        (7,  CASE WHEN wagetype = 1 THEN Jul END, CASE WHEN wagetype = 46 THEN Jul END),
        (8,  CASE WHEN wagetype = 1 THEN Aug END, CASE WHEN wagetype = 46 THEN Aug END),
        (9,  CASE WHEN wagetype = 1 THEN Sep END, CASE WHEN wagetype = 46 THEN Sep END),
        (10, CASE WHEN wagetype = 1 THEN Oct END, CASE WHEN wagetype = 46 THEN Oct END),
        (11, CASE WHEN wagetype = 1 THEN Nov END, CASE WHEN wagetype = 46 THEN Nov END),
        (12, CASE WHEN wagetype = 1 THEN Dec END, CASE WHEN wagetype = 46 THEN Dec END)
    ) AS ca(mon, wage1, wage46)
    WHERE wagetype IN (1, 46)
    GROUP BY pno, mon
), cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY pno ORDER BY wage1 DESC, mon ASC) AS rn
    FROM cte1
)
SELECT *
FROM cte2
WHERE rn = 1