MySQL/PHP根据日期范围和用户id分组结果

你好，这是我的困境。我有两个表，一个叫users，一个叫user_betting。我使用的用户将他们的名字内连接到下面链接中的结果。下面是user_betting的表结构:

id
handicapper_id
game_id
sport
date_placed
ML_team
ML_decision
ML_points
ML_awarded
OU_team
OU_decision
OU_points
OU_awarded
SPREAD_team
SPREAD_decision
SPREAD_points
SPREAD_awarded

如果您浏览此页。http://wewatchcappers.com/rankings_full.php?sport=MLB你可以看到目前我可以拉30/60/90天的数据。然而，我的方法不允许我对最高点进行排序。基本上需要做的是，我需要计算他们在任何*_decision中出现赢/平/输这个词的次数。积分需要是* _奖励的总和。这些可以是正数也可以是负数。

我相信我将需要输入到一个数组，然后排序，但我不知道最好的方法去做这个。有人能帮帮我吗?

这是我目前的做法。

<?php foreach($conn->query("SELECT * FROM users INNER JOIN user_betting ON users.id=user_betting.handicapper_id WHERE sport = '$sport' AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() GROUP BY user_betting.handicapper_id") as $rankings) { ?>
                            <tr>
                                <td><a href="<?php echo $rankings['username'];?>"><?php echo $rankings['username']; ?></a></td>
                                <td align="center">
                                <?php       
                                $ml30wins = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND ML_decision = 'win' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() ")->fetchColumn(); 
                                $ou30wins = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND OU_decision = 'win' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() ")->fetchColumn(); 
                                $spread30wins = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND SPREAD_decision = 'win' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() ")->fetchColumn(); 
                                echo $ml30wins+$ou30wins+$spread30wins;
                                ?>
                                </td>
                                <td align="center">
                                <?php       
                                $ml30loss = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND ML_decision = 'loss' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()")->fetchColumn(); 
                                $ou30loss = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND OU_decision = 'loss' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()")->fetchColumn(); 
                                $spread30loss = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND SPREAD_decision = 'loss' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()")->fetchColumn(); 
                                echo $ml30loss+$ou30loss+$spread30loss;
                                ?></td>
                                <td align="center">
                                <?php       
                                $ml30ties = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND ML_decision = 'tie' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() ")->fetchColumn(); 
                                $ou30ties = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND OU_decision = 'tie' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()")->fetchColumn(); 
                                $spread30ties = $conn->query("SELECT count(*) FROM user_betting WHERE sport = '$sport' AND SPREAD_decision = 'tie' AND handicapper_id = ".$rankings['handicapper_id']." AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()")->fetchColumn(); 
                                echo $ml30ties+$ou30ties+$spread30ties;
                                ?>
                                </td>
                                <td align="center">
                                <?php 
                                $ml30query = mysql_query('SELECT SUM(ML_awarded) AS ml30_sum FROM user_betting WHERE handicapper_id = '.$rankings['handicapper_id'].' AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()'); 
                                $ml30row = mysql_fetch_assoc($ml30query); 
                                $ml30sum = $ml30row['ml30_sum'];
                                $ou30query = mysql_query('SELECT SUM(OU_awarded) AS ou30_sum FROM user_betting WHERE handicapper_id = '.$rankings['handicapper_id'].' AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()'); 
                                $ou30row = mysql_fetch_assoc($ou30query); 
                                $ou30sum = $ou30row['ou30_sum'];
                                $spread30query = mysql_query('SELECT SUM(SPREAD_awarded) AS spread30_sum FROM user_betting WHERE handicapper_id = '.$rankings['handicapper_id'].' AND date_placed BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()'); 
                                $spread30row = mysql_fetch_assoc($spread30query); 
                                $spread30sum = $spread30row['spread30_sum'];
                                echo round($ml30sum+$ou30sum+$spread30sum,2);
                                ?>
                                </td>
                            </tr>
                            <?php } ?>