mysql分组查询_MySQL分组查询

1.创建表

create table score(

id int,

player varchar(20),

position varchar(20),

score float

);

insert into score values(1,'哈登','guard',30.0),(2,'杜兰特','vanguard',29.1),(3,'詹姆斯','vanguard',28.3),(4,'安东尼-戴维斯','vanguard',27.8),(5,'利拉德','guard',27.2);

2.分组

SELECT 字段名1,........

FROM 表名

GROUP BY 字段名1......[HAVING 条件表达式]

(2)对球员位置归类，显示每一位置的总分

select position,sum(score) from score group by position;

(3) 查询得分大于29的球员

select player,sum(score) from score group by player having sum(score)>29.0;

分组之后不能用where过滤要用having，having要用函数

(4)查询球员得分大于27小于30

select player,sum(score) from score where score>27 group by player having sum(score)< 30;

2.使用LIMIT 限制查询结果的数量

SELECT 字段名1....... FROM 表名 LIMIT [OFFSET] 记录数

select from score limit 4;

select from score limit 3,2;

3.为表取别名

SELECT * FROM 表名 [AS] 别名;

4.函数列表