1. 执行顺序
写sql的顺序 :select -> from-> where->group by->having->order by.
解析器执行顺序:from-> where->group by->having->select->order by.
group by a,b,c 则a,b,c三个字段是按照先按a字段对数据行进行排序,再按b字段对那些字段a为同一个值的数据行进行排序,再再按c字段对那些字段a为同一个值且字段b为同一个值的数据行进行排序
order by a,b,c 也是如此
2. 建表
假设有一个表:reward(奖励表),表结构如下:
CREATE TABLE reward (
id int(11) NOT NULL AUTO_INCREMENT,
uid int(11) NOT NULL COMMENT '用户uid',
money decimal(10, 2) NOT NULL COMMENT '奖励金额',
datatime datetime NOT NULL COMMENT '时间',
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = '奖励表';
3. 表数据
insert into reward (id,uid,money ,datatime ) values(1,111,100,now());
insert into reward (id,uid,money ,datatime ) values(2,111,200,now());
insert into reward (id,uid,money ,datatime ) values(3,111,300,now());
insert into reward (id,uid,money ,datatime ) values(4,222,100,now());
insert into reward (id,uid,money ,datatime ) values(6,222,300,now());
insert into reward (id,uid,money ,datatime ) values(7,222,400,now());
insert into reward (id,uid,money ,datatime ) values(8,222,600,now());
insert into reward (id,uid,money ,datatime ) values(9,333,100,now());
insert into reward (id,uid,money ,datatime ) values(10,333,300,now());
insert into reward (id,uid,money ,datatime ) values(11,333,400,now());
insert into reward (id,uid,money ,datatime ) values(12,333,600,now());
1 111 300.00 2021-10-01 12:05:07
2 111 200.00 2021-10-01 12:08:17
3 111 300.00 2021-10-01 12:08:17
4 222 100.00 2021-10-01 12:08:17
6 222 300.00 2021-10-01 12:08:17
7 222 400.00 2021-10-01 12:08:17
8 222 600.00 2021-10-01 12:08:17
9 333 100.00 2021-10-01 12:08:17
10 333 300.00 2021-10-01 12:08:17
11 333 400.00 2021-10-01 12:08:17
12 333 600.00 2021-10-01 12:08:17
4.查询
select * from reward ;
-- 先按照uid分组再排序 ,显示的是每个分组的第一条
select * from reward group by uid;
1 111 300.00 2021-10-01 12:05:07
4 222 100.00 2021-10-01 12:08:17
9 333 100.00 2021-10-01 12:08:17
4.1查询uid 分组 后最大的 money
方法1 :先分组 后排序
select id, uid ,max(money ) from reward group by uid order by id desc ;
9 333 600.00
4 222 600.00
1 111 300.00
方法2 :子查询
SELECT
r.id,
r.uid,
max(r.money),
r.datatime
FROM (SELECT
id,
uid,
money,
datatime
FROM reward
ORDER BY money DESC ) r
GROUP BY r.uid
查询结果:
1 111 300.00 2021-10-01 12:05:07
4 222 600.00 2021-10-01 12:08:17
9 333 600.00 2021-10-01 12:08:17
4.2不符合预期的情况:
SELECT
r.id,
r.uid,
r.money,
r.datatime
FROM (SELECT
id,
uid,
money,
datatime
FROM reward
ORDER BY money desc ) r
GROUP BY r.uid
ORDER BY r.money DESC;
查询结果:
1 111 300.00 2021-10-01 12:05:07
9 333 100.00 2021-10-01 12:08:17
4 222 100.00 2021-10-01 12:08:17