MySQL常用聚合函数

时间:2022-05-13 20:14:57

官方文档:Aggregate (GROUP BY) Functions

Name Description
AVG() Return the average value of the argument
BIT_AND() Return bitwise AND
BIT_OR() Return bitwise OR
BIT_XOR() Return bitwise XOR
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
GROUP_CONCAT() Return a concatenated string
MAX() Return the maximum value
MIN() Return the minimum value
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
SUM() Return the sum
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance

1.AVG

AVG(col)

返回指定列的平均值

2.COUNT

COUNT(col)
返回指定列中非NULL值的个数
 

3.MIN/MAX

MIN(col):返回指定列的最小值
MAX(col):返回指定列的最大值

4.SUM

SUM(col)
返回指定列的所有值之和

5.GROUP_CONCAT

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
返回由属于一组的列值连接组合而成的结果
SELECT id,GROUP_CONCAT(name) FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(name SEPARATOR ';') FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(DISTINCT name SEPARATOR ';') FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(name order by id) FROM my_table GROUP BY id;
SELECT id,GROUP_CONCAT(name order by id SEPARATOR ';') FROM my_table GROUP BY id;

  

注:MySQL默认对结果限制在1024,会自动截断,可以通过变量group_concat_max_len设置,坑!!