分组查询

-- select 字段列表 from 表名 [WHERE 分组前条件限定] group by 对谁分组 [HAVING 分组后过滤条件]

-- 对home分组求出 age年龄平均值
SELECT  home, AVG(age) from dim GROUP BY home;

-- 对home分组求出年龄平均值并且求出各有多少人
SELECT home, avg(age),count(*) from dim GROUP BY home;

-- 对home分组求出年龄的平均值(年龄大于30)并求出各自多少人
SELECT home, avg(age),count(*) from dim WHERE age > 30 GROUP BY home;  

-- 对home分组求出年龄的平均值(年龄大于30)求出多少人,分组后人数要大于2才分组
SELECT home, AVG(age), COUNT(*) from dim WHERE age > 30 GROUP BY home HAVING COUNT(*) > 2;

您可能还喜欢...