T-SQL 中的逐行最大值 [英] Row-wise maximum in T-SQL

问题描述

我有一个包含几列的表格，对于每一行，我想要最大值:

-- 表:+----+----+----+----+----+|身份证 |C1 |C2 |C3 |C4 |+----+----+----+----+----+|1 |1 |2 |3 |4 ||2 |11 |10 |11 |9 ||3 |3 |1 |4 |1 ||4 |0 |2 |1 |0 ||5 |2 |7 |1 |8 |+----+----+----+----+----+-- 想要的结果:+----+---------+|身份证 |row_max |+----+---------+|1 |4 ||2 |11 ||3 |4 ||4 |2 ||5 |8 |+----+---------+

如果有两列或三列，我只需将其写在 iif 或 CASE 语句中即可.

选择ID, iif(C1 > C2, C1, C2) row_max从表

但是随着更多的列，这会很快变得麻烦.有没有一种很好的方法来获得这个行式最大值?在 R 中，这被称为并行最大值"，所以我喜欢类似

选择ID, pmax(C1, C2, C3, C4) row_max从表

解决方案

如何取消数据透视以获得结果?你说的是 tsql 但不是什么版本的 SQL Server.在 SQL Server 2005+ 中，您可以使用 CROSS APPLY 将列转换为行，然后获取每一行的最大值:

select id, row_max = max(val)从你的桌子交叉申请(选择 c1 联合所有选择 c2 union all选择 c3 union all选择 c4) c (val)按 ID 分组

请参阅SQL Fiddle with Demo.请注意，这可以通过使用表值构造函数来缩写.>

这也可以通过 UNPIVOT 函数:

select id, row_max = max(val)从你的桌子转轴(值用于输入 (C1, C2, C3, C4)) piv按 ID 分组

参见SQL Fiddle with Demo.两个版本都给出了结果:

<代码>|身份证 |row_max ||----|---------||1 |4 ||2 |11 ||3 |4 ||4 |2 ||5 |8 |

I've got a table with a few columns, and for each row I want the maximum:

-- Table:
+----+----+----+----+----+
| ID | C1 | C2 | C3 | C4 |
+----+----+----+----+----+
|  1 |  1 |  2 |  3 |  4 |
|  2 | 11 | 10 | 11 |  9 |
|  3 |  3 |  1 |  4 |  1 |
|  4 |  0 |  2 |  1 |  0 |
|  5 |  2 |  7 |  1 |  8 |
+----+----+----+----+----+


-- Desired result:
+----+---------+
| ID | row_max |
+----+---------+
|  1 |       4 |
|  2 |      11 |
|  3 |       4 |
|  4 |       2 |
|  5 |       8 |
+----+---------+

With two or three columns, I'd just write it out in iif or a CASE statement.

select ID
  , iif(C1 > C2, C1, C2) row_max
from table

But with more columns this gets cumbersome fast. Is there a nice way to get this row-wise maximum? In R, this is called a "parallel maximum", so I'd love something like

select ID
  , pmax(C1, C2, C3, C4) row_max
from table

解决方案

What about unpivoting the data to get the result? You've said tsql but not what version of SQL Server. In SQL Server 2005+ you can use CROSS APPLY to convert the columns into rows, then get the max value for each row:

select id, row_max = max(val)
from yourtable
cross apply
(
  select c1 union all
  select c2 union all
  select c3 union all
  select c4
) c (val)
group by id

See SQL Fiddle with Demo. Note, this could be abbreviated by using a table value constructor.

This could also be accomplished via the UNPIVOT function in SQL Server:

select id, row_max = max(val)
from yourtable
unpivot
(
  val
  for col in (C1, C2, C3, C4)
) piv
group by id

See SQL Fiddle with Demo. Both versions gives a result:

| id | row_max |
|----|---------|
|  1 |       4 |
|  2 |      11 |
|  3 |       4 |
|  4 |       2 |
|  5 |       8 |

这篇关于T-SQL 中的逐行最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！