SQL:显示计算结果的列名

Question

所以我正在计算一系列列的最大值，我想知道显示结果列名称的最佳方式是什么。

示例表：

hour1      hour2      hour3
16         10         5

我的查询如下：

(SELECT Max(v) 
FROM (VALUES (hour1) , (hour2) , (hour3)) 
AS VALUE (v)) AS PEAK_VALUE

请注意，这在另一个查询中。

期望输出：

PEAK_VALUE      PEAK_HOUR
16              hour1      

我还想对PEAK_VALUE列进行进一步的计算。例如，将其除以2以获得此输出：

PEAK_VALUE     HALF_VALUE  PEAK_HOUR
16             8          hour1

Answer 1

尽管您的查询语法有一些问题，但您几乎得到了它。您需要将列名添加到取消透视中。之后用户row_number()找到最大值

---

SELECT PEAK_VALUE = v,
HALF_VALUE = v / 2,
PEAK_HOUR = c
FROM
(
SELECT *, rn = row_number() over (order by v desc)
FROM   example
CROSS APPLY
(
VALUES ('hour1', hour1) , ('hour2', hour2) , ('hour3', hour3)
)  AS VALUE (c, v)
) v
WHERE rn = 1

dbfiddle

Answer 2

只是另一个选项sans row_number((

示例

Select top 1
Peak_Value = Hour
,Half_Value = Hour/2
,Peak_Hour  = Item
From  YourTable A
Cross Apply (values ('hour1',hour1)
,('hour2',hour2)
,('hour3',hour3)
) B (Item,Hour)
Order by Hour Desc

结果

Peak_Value  Half_Value  Peak_Hour
16          8           hour1

Answer 3

使用UNPIVOT的另一种方法。

DECLARE @table table(hour1 int, hour2 int, hour3 int)
insert into @table
values(16,10,5)
SELECT top 1 max(val) as peak_value, max(val) /2 as Half_Value, [hour]
FROM @table
unpivot (val for [hour] in ([hour1],[hour2],[hour3])) as t
group by [hour]
order by max(val) desc

peak_value	Half_value	小时
16	8	小时1

您可以在CROSS APPLY中推送一个完整的取消抓取查询，然后进行进一步的计算：

SELECT t.*, v.*
FROM yourTable t
CROSS APPLY (
SELECT TOP (1)
v.PEAK_VALUE,
HALF_VALUE = v.PEAK_VALUE / 2,
v.PEAK_HOUR,
FROM (VALUES
('hour1', hour1),
('hour2', hour2),
('hour3', hour3)
) AS v(PEAK_HOUR, PEAK_VALUE)
ORDER BY PEAK_VALUE DESC
) AS v

这与其他答案略有不同，因为它将计算每行的子查询