查询玩家充值后第一笔购买的东西
这个查询初看比较棘手,仔细分析之后发现有比较巧妙的方式解决。
* 充值表 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)