mysql使用group_mysql使用group_by

GROUP BY必须得配合聚合函数来用，分组之后你可以计数(COUNT)，求和(SUM)，求平均数(AVG)等

常用聚合函数

count() 计数

sum() 求和

avg() 平均数

max() 最大值

min() 最小值

语法：

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

如：

-- 抽奖次数

SELECT

user.`name` AS user_name,

count(*) AS lottery_count,

location.`name` AS location_name,

user_id

FROM

space_turntable_log

LEFT JOIN user ON user.id = space_turntable_log.user_id

LEFT JOIN location ON location.id = user.location_id

GROUP BY

user_id;

HAVING

当然提到GROUP BY 我们就不得不提到HAVING，HAVING相当于条件筛选，但它与WHERE筛选不同，HAVING是对于GROUP BY对象进行筛选。

-- 抽奖次数

SELECT

user.`name` AS user_name,

count(*) AS lottery_count,

location.`name` AS location_name,

user_id

FROM

space_turntable_log

LEFT JOIN user ON user.id = space_turntable_log.user_id

LEFT JOIN location ON location.id = user.location_id

GROUP BY

user_id HAVING lottery_count>2;

SELECT

yx_user. NAME AS 用户名,

count('*') AS 发布总条数,

yx_location. NAME AS 城市

FROM

yx_space_podcast

LEFT JOIN yx_user ON yx_user.id = yx_space_podcast.user_id

LEFT JOIN yx_location ON yx_user.location_id = yx_location.id

WHERE

yx_space_podcast.created_at > UNIX_TIMESTAMP('2019-04-01 00:00:00')

AND yx_space_podcast.created_at < UNIX_TIMESTAMP('2019-04-27 00:00:00')

AND yx_space_podcast.is_delete = 0

GROUP BY

yx_space_podcast.user_id

HAVING

发布总条数 > 1