mysql - 使用语句查找第一个订单 (SQL)

Question

我有 2 个表格，看起来像:

user_dataset
+------+---------------+------------+
|userid| Register_time |    Country | 
+------+---------------+------------+
|    1 |  03/02/17     |      TW    |     
|    2 |  20/03/17     |      SG    |     
|    3 |  26/03/17     |      PH    |   
|    4 |  05/02/17     |      VN    | 
|    5 |  01/10/17     |      ID    | 
|    6 |  03/09/17     |      MY    |  
|   ...|  ........     |      ...   | 
+------+---------------+------------+

order_dataset
+--------+--------+------------+--------+------------+
|orderid | userid |    itemid  |  gmv   | order_time |
+--------+--------+------------+--------+------------+
|1030132 |  3     |  3366770   |  27,0  |  24/04/17  |   
|1030137 |  5     |  6130641   |  69,0  |  02/02/17  |     
|1030147 |  1     |  6770063   |  87,0  |  25/04/17  |    
|1030153 |  6     |  4193426   |  82,0  |  05/11/17  |   
|1030155 |  4     |  8825994   |  29,0  |  03/07/17  |   
|1030160 |  2     |  5660916   |  44,0  |  30/01/17  |   
|....... | ...    |      ...   |  ...   |     ...    |   
+--------+--------+------------+--------+------------+

我需要编写一个查询:查找每个用户的第一笔订单 GMV，如果存在平局，则使用 orderid 较低的订单。

如何实现这一目标？预先感谢您

Answer 1

您可以使用相关子查询:

select u.*,
       (select o.gmv
        from order_dataset o
        where o.userid = u.userid
        order by order_time, orderid
        limit 1
       ) as earliest_gmv
from user_dataset u;

在 order_dataset(userid, order_time, orderid) 上建立索引将大大提高性能。