mysql - 如何在没有组的 MIN() 和 MAX() 的情况下获取项目组的 STDDEV()

Question

我有一张 table

| id | user | bottle |  count |
| 1  | foo  | beer   |    2   |
| 2  | bar  | beer   |    5   |
| 3  | som1 | beer   |    6   |
| 4  | som2 | beer   |    4   |
| 5  | som1 | wine   |    1   |

等等

如果没有 count 的 MIN() 和 MAX() 值，我如何获得 STDDEV() > 每组(啤酒、 Wine 等)？
即我如何获得

| bottle |      stddev     |

|  beer  |stddev of 2 and 4|

?

另外，如何获取上述查询返回的总行数？

到目前为止我已经尝试过了

SELECT STD(count) as stddev, bottle
FROM drinks WHERE count < (
    SELECT MAX(count) FROM drinks)
              AND count > (
    SELECT MIN(count) FROM drinks)
GROUP BY bottle;

但这适用于整个表的 MAX() 而不仅仅是分组依据

Answer 1

使用聚合和连接引入极值，然后过滤掉它们并计算标准差:

select bottle, STDEV(d.count)
from drd.inks d join
     (select bottle, MIN(count) as minc, MAX(count) as maxc
      from drinks
      group by bottle
     ) dsum
     on d.bottle = dsum.bottle and
        d.count not in (dsum.minc, dsum.maxc)
group by d.bottle