前提是A类用户用手机发送B类用户的手机号码到某网站推荐参加某项活动
A类用户有很多,B类用户也有很多,B类用户可能被多个A类用户推荐,也就是一个B类用户的被多个A类用户发送其号码
表结构如下
A类用户手机号码 发送时间 B类用户手机号码要求是查询出以下数据
1.推荐人号码——(A类用户)
2.推荐量——推荐了几个B类用户
4.奖品数量(推荐量<3,奖品数量为0;3=<推荐量<5,奖品数量为1;5=<推荐量<5,奖品数量为2;推荐量>=8,奖品数量为3)
注:如B类用户被重复推荐只计入最先推荐的A类用户的推荐量中
很急啊
请大家帮忙
A类用户有很多,B类用户也有很多,B类用户可能被多个A类用户推荐,也就是一个B类用户的被多个A类用户发送其号码
表结构如下
A类用户手机号码 发送时间 B类用户手机号码要求是查询出以下数据
1.推荐人号码——(A类用户)
2.推荐量——推荐了几个B类用户
4.奖品数量(推荐量<3,奖品数量为0;3=<推荐量<5,奖品数量为1;5=<推荐量<5,奖品数量为2;推荐量>=8,奖品数量为3)
注:如B类用户被重复推荐只计入最先推荐的A类用户的推荐量中
很急啊
请大家帮忙
with tmp as
(
select '13412345678' user_a, sysdate - 5 send_time, '15901234567' user_b from dual
union all
select '13412345678' user_a, sysdate - 4 send_time, '15911234567' user_b from dual
union all
select '13412345678' user_a, sysdate - 3 send_time, '15921234567' user_b from dual
union all
select '13412345678' user_a, sysdate - 2 send_time, '15931234567' user_b from dual
union all
select '13412345678' user_a, sysdate - 1 send_time, '15941234567' user_b from dual
union all
select '13412345679' user_a, sysdate - 1 send_time, '15901234567' user_b from dual
union all
select '13412345679' user_a, sysdate send_time, '15911234567' user_b from dual
union all
select '13412345679' user_a, sysdate send_time, '15921234567' user_b from dual
union all
select '13412345679' user_a, sysdate send_time, '15971234567' user_b from dual
union all
select '13412345679' user_a, sysdate send_time, '15981234567' user_b from dual
union all
select '13412345679' user_a, sysdate send_time, '15991234567' user_b from dual
)
select user_a, count(user_b) tjs, (case when count(user_b) < 3 then 0 when count(user_b) < 5 then 1 when count(user_b) < 8 then 2 else 3 end) jiangpin
from tmp mt
where send_time = (select min(send_time) from tmp where user_b = mt.user_b)
group by user_a;USER_A TJS JIANGPIN
---------------- ---------- ----------
13412345678 5 2
13412345679 3 1
select 13333333333 aiphoe, '20100501'stat_date, 13444444444 biphone from dual union all
select 13555555555 aiphoe, '20100502'stat_date, 13444444444 biphone from dual union all
select 13777777777 aiphoe, '20100501' stat_date, 13666666666 biphone from dual union all
select 13777777777 aiphoe, '20100501' stat_date, 13888888888 biphone from dual union all
select 13999999999 aiphoe, '20100501' stat_date, 13000000000 biphone from dual
)
--1.推荐人号码——(A类用户)
select distinct(aiphoe) from t;
--2.推荐量——推荐了几个B类用户
select aiphoe ,count(1) 推荐量 from t group by aiphoe;
--奖品数量(推荐量<3,奖品数量为0;3=<推荐量<5,奖品数量为1;5=<推荐量<5,奖品数量为2;推荐量>=8,奖品数量为3)select aiphoe,
case when amount < 3 then 0
when amount >= 3 and amount < 5 then 1
when amount >= 5 and amount < 8 then 2
when amount > 8 then 3
end
from ( select aiphoe ,count(1) amount from t group by aiphoe ) tt;
count(1) counts,
case when count(1)<3 then 0 when count(1)<5 then 1 when count(1)<8 then 2 else 3 end prizes
from t tt
where not exists(select 1 from t where bno=tt.bno and time<tt.time)
group by ano
为什么是bno=tt.bno and time<tt.time呢