按组检索3个值 [英] Group By Retrieve 3 Values

问题描述

我有以下查询

  SELECT Cod，
 MIN（Id）AS id_Min，
 MAX （Id）AS id_Max，
  - 在中间检索值
 COUNT（*）AS Tot 
 
 FROM表a（NOLOCK）
 GROUP BY Cod 
 HAVING COUNT（*）= 3 
  

我如何检索min和max之间的值已经完成了最小和最大？

实例

  Cod | Id 
 
堆栈10 
堆栈15 
堆栈11 
溢出100 
溢出120 
溢出15 
  

必需输出

  Cod | Min | Value_In_The_Middle |最大
 
堆栈10 11 15 
溢出15 100 120 
  

解决方案

我还没有测试过这个，但我认为这可行

  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（）不溢出的情况（如 Bogdan Sahlean 在评论中指出）。

I have following query

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

how could i retrieve value between min and max as i have done for min and max?

EXAMPLES

Cod      |  Id

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

Required output

Cod         | Min   | Value_In_The_Middle  |    Max

Stack         10          11                    15
Overflow      15          100                   120

解决方案

I haven't tested this but I think this would work

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

This trick only works when you have 3 values and the SUM() doesn't overflow (like Bogdan Sahlean points out in the comments).

这篇关于按组检索3个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！