查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age < 45
group by workaddress having address_count >= 3;
查询某一列的行数,某一列的最值和平均数(会忽略NULL值):
select count(id)/count(*) from product where price > 100;
select sum(price)/max(price)/min(price)/avg(price) from product where price < 100;
按楼层,办公室分类查询数量(顺序是先分组再查数量):
select building_id,office_id,count(id) from product
group by building_id,office_id;
对分组后的数据进行筛选并排序:
select office_id, count(id) as cnt from product
group by office_id having cnt > 4 order by cnt;