这些年写过的花式sql 第2句统计用户返佣金排名-526互联

第2句统计用户返佣金排名

继续欢迎批评指正建议 ^^

需求：

曾经的活动是购买订单返流量（日志在 flow_rebate_log），后来改成返佣金（日志在 money_rebate_log）。
现在需要按照用户返佣金额排名，如果不存在金额，则按返流量排名。其他需要列出的字段：username(用户名连接 users表获得)，用户返佣金额，用户返佣金的订单数，用户返流量数，用户返流量的订单数

SELECT r.*,u.username,
       flow_order_num + money_order_num as order_num,flow_order_money + money_order_money as order_money
from
        (
            (
							SELECT f.uid,rebate_flow,flow_order_num,rebate_money,money_order_num,f.money as flow_order_money,m.money as money_order_money
								from
									(SELECT sum(rebate_value) as rebate_flow,count(*) as flow_order_num,uid,money FROM `flow_rebate_log` group by uid) f
									left join
									(SELECT sum(consume_money) as rebate_money,count(*) as money_order_num,uid,money FROM `money_rebate_log` group by uid) m 
									on m.uid = f.uid
						)
								
						union

						(
							SELECT m.uid,rebate_flow,flow_order_num,rebate_money,money_order_num,f.money as flow_order_money,m.money as money_order_money
							from
									(SELECT sum(rebate_value) as rebate_flow,count(*) as flow_order_num,uid,money FROM `flow_rebate_log`  group by uid ) f  
									right join
									(SELECT sum(consume_money) as rebate_money,count(*) as money_order_num,uid,money FROM `money_rebate_log`  group by uid) m 
									on m.uid = f.uid
						)
        ) r

left join 
`users` u
on u.id = r.uid

排序在php代码里，因为想利用 TP框架的分页功能返回给前端。

//$tb = "sql语句";
 $list = Db::table("($tb) t")
        ->order('rebate_money desc,rebate_value desc')
        ->limit(50)
        ->select();

解析：

因为有的用户只出现在 flow_rebate_log,有的只出现在 money_rebate_log, 需要一个全连接。使得每条记录同时包含用户的返流量和返金额信息。而mysql不支持full join，
只能用一个trick ： m (money_rebate_log ) full join f(flow_rebate_log) 相当于