mysql查询实例（查询玩家充值后第一笔购买的东西）

查询玩家充值后第一笔购买的东西

这个查询初看比较棘手，仔细分析之后发现有比较巧妙的方式解决。
* 充值表 rechange

uid	date_time	count
10086	2	2
10086	5	3
10086	7	4

* 购买表 mall_flow

uid	date_time	item_id
10086	5	1001
10086	8	1002
10086	9	1001

约束：充值时间应<=购买时间。
解法：
分析：如果我们将两个时间放在一起排序，离一次充值最近的购买和离一次购买最近的充值都有可能满足需求，那么当两者求交集就是所求的最终结构

离充值最近的购买

select a.date_time as x,min(b.date_time) as y from rechange as a join mall_flow as b using(uid) where
a.date_time <= b.date_time group by a.date_time

x    y
2    5
5    5
7    8

离购买最近的充值

select max(a.date_time) as x ,b.date_time as y from rechange as a join mall_flow as b using(uid) where 
a.date_time <= b.date_time group by b.date_time

x    y
5    5
7    8
7    9

合并两者得到：

x    y
5    5
7    8

select * from(
  select a.date_time as x,min(b.date_time) as y from rechange as a join mall_flow as b using(uid) where
  a.date_time <= b.date_time group by a.date_time
)as A join(
  select max(a.date_time) as x ,b.date_time as y from rechange as a join mall_flow as b using(uid) where 
  a.date_time <= b.date_time group by b.date_time
)as B using(x,y)