我有以下查询
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
我怎样才能像我对最小值和最大值所做的那样检索最小值和最大值之间的值?
EXAMPLES
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
我还没有测试过,但我认为这会起作用
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() 不会溢出时才有效(例如博格丹·萨赫林 https://stackoverflow.com/users/924077/bogdan-sahlean在评论中指出)。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)