mysql distinct 使用注意事项

想查询最近N条订单记录中订单来源（

order_from

）有哪几种，刚开始使用的SQL命令是

select distinct order_from from order order by id desc limit 1000  
           

发觉查询的记录很多 明显1000条外的数据也查出来了。 后面改成下面的SQL

select distinct order_from from (select order_from from order order by id desc limit 1000) 
           

这时就对了。

演示上述现象

select * from distinct_test;
+----+------+
| id | a    |
+----+------+
|  1 | aaa  |
|  2 | aaa  |
|  3 | bbb  |
|  4 | bbb  |
|  5 | ccc  |
|  6 | ddd  |
|  7 | ddd  |
|  8 | foo  |
|  9 | bar  |
+----+------+

select distinct a  from distinct_test order by id limit 4;
+------+
| a    |
+------+
| aaa  |
| bbb  |
| ccc  |
| ddd  |
+------+

select distinct a  from (select a from distinct_test order by id limit 4) a;
+------+
| a    |
+------+
| aaa  |
| bbb  |
+------+
           

另外在5.7版本Mysql中第一种写法会报错 如下所示

select version();
+-----------+
| version() |
+-----------+
| 5.7.12    |
+-----------+
select distinct a from distinct_test order by id limit 4;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.distinct_test.id' which is not in SELECT list; this is incompatible with DISTINCT