具有附加限制的多个参数的 SQL Server Max() 值

Question

我想获得每个参数的最大值，但在每个参数的单独限制内。这是示例表:

+-----------+------+------+------+
| VehicleID | Par1 | Par2 | Par3 |
+-----------+------+------+------+
|         1 |    6 | 9    | NULL |
|         2 |    7 | 7    | 1    |
|         3 |   10 | 3    | 2    |
|         1 |    8 | NULL | 7    |
|         2 |   10 | 1    | 6    |
|         3 |    6 | 8    | 9    |
|         1 |   10 | 4    | 11   |
|         2 |   11 | NULL | NULL |
|         3 |    3 | 6    | 12   |
+-----------+------+------+------+

想法是获取每个 vehicleid 分组的 Par1、Par2 和 Par3 的最大值，但最大值应低于 9。如果仅针对 1 个参数，查询将是

select vehicleID, max(Par1) from Table1 where Par1<9 group by VehicleID

有没有办法用 1 个查询来做到这一点，所以结果应该是:

+-----------+---------+---------+---------+
| VehicleID | MaxPar1 | MaxPar2 | MaxPar3 |
+-----------+---------+---------+---------+
|         1 |       8 |       4 |       7 |
|         2 |       7 |       7 |       6 |
|         3 |       6 |       8 |       2 |
|           |         |         |         |
+-----------+---------+---------+---------+

db<>fiddle

Answer 1

条件聚合是解决此问题的最简单方法。

declare @Something table
(
    VehicleID int
    , Par1 int
    , Par2 int
    , Par3 int
)

insert @Something values
(1,  6, 9   , NULL)
, (2,  7, 7   , 1   )
, (3, 10, 3   , 2   )
, (1,  8, NULL, 7   )
, (2, 10, 1   , 6   )
, (3,  6, 8   , 9   )
, (1, 10, 4   , 11  )
, (2, 11, NULL, NULL)
, (3,  3, 6   , 12  )

select VehicleID
    , MaxPar1 = max(case when Par1 < 9 then Par1 end)
    , MaxPar2 = max(case when Par2 < 9 then Par2 end)
    , MaxPar3 = max(case when Par3 < 9 then Par3 end)
from @Something
group by VehicleID