关于MySQL group by的问题

Question

表字段及数据：
id	amount	user_id	create_time
1	100	1	2016-10-11 10:59:33
2	3000	2	2016-10-10 10:59:54
3	3256	1	2016-10-09 11:00:16
4	1546	1	2016-10-08 11:00:38

我想取每个user的第一条记录，
SQL：select * from test group by user_id.

结果：
id	amount	user_id	create_time
1	100	1	2016-10-11 10:59:33
2	3000	2	2016-10-10 10:59:54

然后我发现如果根据时间来排序，无论是顺序还是逆序他都是这个结果，然后我猜想这个结果是不是根据id的大小来的，结果我改了id也是一样，然后我猜想group by 结果是根据记录添加顺序来决定的，当group by的字段出现重复时，只会取第一条记录，不知道这样对不对，求解答？

Answer 1

It’s the same without adding create_time. It’s just because the order in your records is already regular and there is no sorting. In fact, after group by, only the record where user_id appears for the first time in all records is taken out

Answer 2

In what order does your so-called "first record" mean "first"? Database tables themselves do not preset a certain order. Suppose you want to sort by id and id no duplication:

select * from test where id in (select min(id) from test group by user_id);

It should be able to achieve the purpose.

Answer 3

Group by is the result displayed in order of primary key.
To display the latest amount, you need to sort it within the group.

selet * from( select id,amount,user_id,create_time from test order by create_time desc) as t group by user_id

Answer 4

This understanding can be wrong or right.

1. I’m wrong because according to sql standards, if there is grouping in sql, only the grouping column is the valid data.
   select * from test group by user_id. This line means that only the user_id column is the real data, and other columns It’s fake. Even if you select it, you can’t use it.

2. But for MySql, his behavior is exactly as you described.