mysql - 如何联接一个表并限制同一表

Question

公司数据库具有有关表ID，ORDER_DATE，STATUS，CUSTOMER_ID的信息，列名称表名称为ORDERS。

1. Need to extract the info which is not delivered.

2. If there is more than 5 orders select one with lowest order id. 

3.Sort the output in the increasing order of order ID

表

ID,ORDER_DATE,STATUS,CUSTOMER_ID 
10100,1-6-2003,PLACED,344
10101,2-6-2003,DELIVERED,544
10102,3-6-2003,IN TRANSIT,341
10103,4-6-2003,PLACED,343
10104,5-6-2003,IN TRANSIT,312
10105,6-6-2003,PLACED,390
10106,7-6-2003,PLACED,399

我的查询

select ID,ORDER_DATE,STATUS,CUSTOMER_ID from ORDERS where STATUS in ('IN TRANSIT', 'PLACED')order by ORDER_DATE desc limit 5;

我出来

52 2016-09-04 PLACED 87 
10 2016-09-04 PLACED 252 
40 2016-09-04 PLACED 989 
62 2016-09-04 IN TRANSIT 780 
45 2016-09-04 IN TRANSIT 527

预期中>

10100 2003-01-06 PLACED 363
10101 2003-01-06 PLACED 128
10102 2003-01-06 IN TRANSIT 181
10106 2003-01-07 IN TRANSIT 278
10120 2003-01-07 PLACED 114

Answer 1

我认为您只需要：

select ID, ORDER_DATE, STATUS, CUSTOMER_ID from ORDERS where STATUS <> 'DELIVERED' order by ID limit 5;