sql語句怎么實現聚集函數嵌套

有時辰我們在寫sql語句的時辰，想實現堆積函數嵌套，怎么實現呢，下面來分享一下方式

東西/原料

1
第一步我們在寫sql語句的時辰，寫sum(min(a))這種函數嵌套格局會呈現語法錯誤，下面以test表為例，來實現堆積函數嵌套，如下圖所示：
2
第二步輸入“select sum(diff) from (SELECT max(second)-min(second) as diff from test GROUP BY name) as a”語句，如許就實現了sum函數和max函數、min函數的嵌套，這句sql語句的意思是按name字段進行分組，并求出每組的最年夜值和最小值之差，對所有組的差值進行乞降，如下圖所示：
3
第三步運行sql語句，我們可以看到成果為38，張三的最年夜最小差值為34，李四的為4，王五的為0，加起來成果準確，如下圖所示：
4
第四步我們也可以輸入“select avg(diff) from (SELECT max(second)-min(second) as diff from test GROUP BY name) as a”語句，計較每組最年夜值和最小值之差的平均值，成果為12.6667，如下圖所示：
5
第五步輸入“select count(diff) from (SELECT max(second)-min(second) as diff from test GROUP BY name) as a”語句，計較分了幾組，如下圖所示：
6
第六步輸入“select avg(diff) from (SELECT max(second) as diff from test GROUP BY name) as a”語句，計較每組的最年夜值平均值，如下圖所示：