时间:2020年8月18日16:46:52
select * from(
-- 用户信息
select * from(
select u.id,from_unixtime(u.c_time),from_unixtime(u.last_login_time),
ua.available_total_money,
CASE WHEN p.is_novice = 1
THEN up.money
ELSE 0 end new_invest
from sl_user u
left join sl_user_account ua on u.id = ua.user_id
left join sl_user_plan up on up.user_id = u.id
left join sl_plan p on p.id = up.plan_id
where p.is_novice = 1 and up.status > -1
) dd
left join
-- 充值-投资金额
(
select aa.user_id,aa.top_money,bb.plan_money from (
select user_id,sum(money) top_money from sl_top_up
where status = 2 and pay_type=9
group by user_id) aa
left join
(select user_id,sum(money) plan_money from sl_user_plan
where status > -1
group by user_id) bb on aa.user_id = bb.user_id
)ee on dd.id = ee.user_id
) qq
right join
-- 匹配
(
select user_id,sum(ten),sum(ten1),sum(ten2),sum(ten3),sum(ten4) from
(
select uc.user_id,uc.coupon_id,uc.money,uc.sn,up.money invest_money,
CASE WHEN coupon_id = 16
THEN up.money
end ten,
CASE WHEN coupon_id = 17
THEN up.money
end ten1,
CASE WHEN coupon_id = 18
THEN up.money
end ten2,
CASE WHEN coupon_id = 19
THEN up.money
end ten3,
CASE WHEN coupon_id = 20
THEN up.money
end ten4
from promote_user_coupon uc
left join sl_user_plan up on uc.sn = up.sn
where uc.is_avail= 2 and uc.coupon_id in (16,17,18,19,20)
)bb group by user_id
)ww on qq.user_id = ww.user_id
-- ----------------------------------------------------------------------
-- 1.红包
select user_id,sum(ten),sum(ten1),sum(ten2),sum(ten3),sum(ten4) from
(
select uc.user_id,uc.coupon_id,uc.money,uc.sn,up.money invest_money,
CASE WHEN coupon_id = 16
THEN up.money
end ten,
CASE WHEN coupon_id = 17
THEN up.money
end ten1,
CASE WHEN coupon_id = 18
THEN up.money
end ten2,
CASE WHEN cou