我有两个表odds
和matches
:
匹配:具有match_id
和match_date
赔率:具有id
、timestamp
、result
、odd_value
、user_id
、match_id
我有一个查询,从这些表中为每个用户获取以下信息:
- 奖金:每个用户的中奖赌注。(当odds.result=1时)
- loss:每个用户输掉的赌注。(当odds.result!=1时)
- 点:每个用户的点。(赔率.oodd_value的总和)
- 奖金:对于每连续5次奖金,我想在这个变量中添加额外的奖金。(针对每个用户)
如何计算奖金?我尝试使用这个查询,但遇到了一个问题:(您可以在这里查看SQL Fiddle)
计算的奖金并不适合所有用户:
第一个用户:(奖金:13,奖金=2)。
第二位用户:(奖金:8,奖金=2)这里的奖金应该是1。
第三位用户:(奖金:14,奖金=3)这里的奖金应该是2。
为什么查询没有正确计算bonus
?
select d.user_id,
sum(case when d.result = 1 then 1 else 0 end) as winnings,
sum(case when d.result = 2 then 1 else 0 end) as loses,
sum(case when d.result = 1 then d.odd_value else 0 end) as points,
f.bonus
FROM odds d
INNER JOIN
(
SELECT
user_id,SUM(CASE WHEN F1=5 THEN 1 ELSE 0 END) AS bonus
FROM
(
SELECT
user_id,
CASE WHEN result=1 and @counter<5 THEN @counter:=@counter+1 WHEN result=1 and @counter=5 THEN @counter:=1 ELSE @counter:=0 END AS F1
FROM odds o
cross join (SELECT @counter:=0) AS t
INNER JOIN matches mc on mc.match_id = o.match_id
WHERE MONTH(STR_TO_DATE(mc.match_date, '%Y-%m-%d')) = 2 AND
YEAR(STR_TO_DATE(mc.match_date, '%Y-%m-%d')) = 2015 AND
(YEAR(o.timestamp)=2015 AND MONTH(o.timestamp) = 02)
) Temp
group by user_id
)as f on f.user_id = d.user_id
group by d.user_id
我不确定你的结果与matches
表、有什么关系
如果需要,可以添加回WHERE
/INNER JOIN
子句。
这是小提琴的链接
以及根据您的意见进行的最后一次迭代:
这里有一个查询:
SET @user:=0;
select d.user_id,
sum(case when d.result = 1 then 1 else 0 end) as winnings,
sum(case when d.result = 2 then 1 else 0 end) as loses,
sum(case when d.result = 1 then d.odd_value else 0 end) as points,
f.bonus
FROM odds d
INNER JOIN
(
SELECT
user_id,SUM(bonus) AS bonus
FROM
(
SELECT
user_id,
CASE WHEN result=1 and @counter<5 AND @user=user_id THEN @counter:=@counter+1
WHEN result=1 and @counter=5 AND @user=user_id THEN @counter:=1
WHEN result=1 and @user<>user_id THEN @counter:=1
ELSE
@counter:=0
END AS F1,
@user:=user_id,
CASE WHEN @counter=5 THEN 1 ELSE 0 END AS bonus
FROM odds o
ORDER BY user_id , match_id
) Temp
group by user_id
)as f on f.user_id = d.user_id
group by d.user_id