如何将用户变量用作包含GROUP BY语句的内部联接查询的计数器？

问题描述

我有2个表个赔率和个匹配项：

matches：具有 match_id 和 match_date

matches : has match_id and match_date

odds ：具有 id ，时间戳，结果， odd_value ， user_id ， match_id

odds : has id, timestamp, result, odd_value, user_id, match_id

我有一个查询，从每个用户的表中获取以下信息：

I had a query that get the following information from those tables for each user:

1. winnings：每个用户。 （当odds.result = 1时）


2. loses：每个用户输掉的赌注。（当odds.result！= 1时）


3. 点数：每个用户的积分。（每个用户的赔率之和。odd_value的总和）。


4. 奖金：对于每连续5笔奖金，我想为该变量添加额外的奖金。 （针对每个用户）

1. winnings : the winning bets for each user. (when odds.result = 1)
2. loses : the lost bets for each user.(when odds.result != 1)
3. points : the points of each user.(the sum of the odds.odd_value) for each user.
4. bonus : for each continuous 5 winnings i want to add extra bonus to this variable. (for each user)

如何计算奖金？
我尝试使用此查询，但遇到了一个问题：（您可以在此处进行检查）

How to calculate bonus?I tried to use this query and I faced a problem : (you can check it here SQL Fiddle)

计算得出的奖金并不适合所有用户：

the calculated bonus are not right for all the users :

第一用户：(奖金：13，奖金= 2）。

first user:(winnings:13, bonus=2).

第二用户：(奖金：8，奖金= 2）奖金应为1。

second user:(winnings:8, bonus=2)bonus here should be 1.

第三位用户：(奖金：14，奖金= 3）奖金在这里应为2。

third user:(winnings:14, bonus=3)bonus here should be 2.

为什么查询不正确计算奖金？

why does the query not calculate the bonus correctly?

        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

推荐答案

我不确定您的与匹配项表相关的结果，

I am not sure how your result related to matches table,

您可以在 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

这篇关于如何将用户变量用作包含GROUP BY语句的内部联接查询的计数器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！