分组依据检索3个值

小编典典

分组依据检索3个值

sql

我有以下查询

SELECT  Cod ,
        MIN(Id) AS id_Min,
        MAX(Id) AS id_Max,
        -- retrieve value in the middle,
        COUNT(*) AS Tot

FROM    Table a ( NOLOCK )        
GROUP BY Cod
HAVING  COUNT(*)=3

我怎样才能像我对min和max所做的那样检索min和max之间的值？

例子

Cod      |  Id

Stack       10
Stack       15
Stack       11
Overflow    100
Overflow    120
Overflow    15

要求的输出

Cod         | Min   | Value_In_The_Middle  |    Max

Stack         10          11                    15
Overflow      15          100                   120

阅读 179

2021-04-28

共1个答案

小编典典

我还没有测试过，但我认为这可以工作

SELECT  Cod ,
        MIN(Id) AS id__Min,
        MAX(Id) AS id_Max,
        SUM(ID)-MAX(Id)-MIN(Id) as id_Middle,
        COUNT(*) AS Tot
FROM    Table a ( NOLOCK )        
GROUP BY Cod
HAVING  COUNT(*)=3

仅当您具有3个值并且SUM（）不会溢出时（如BogdanSahlean在注释中指出），此技巧才起作用。

2021-04-28