想查询最近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
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。