order by 和 group by 可以一起使用吗

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