71

71

关注
发信
关注(28)粉丝(399)

mysql - NHL在SQL中的排名

我有一个基于旧NHL格式生成团队排名的SQL查询。代码的第一部分得到每个分区的前三名，第二部分得到其余的，并按点数/差分对它们进行排序。
这里可以看到：http://rgmgstandings.tk
以下是我的SQL查询：

("(SELECT *, 1 as `SortKey` from `standings_east`
        WHERE pts = (select max(pts)
            from standings_east as t
    where t.`div` = standings_east.`div`))

UNION ALL

(select *, 2 as SortKey from `standings_east`
        where team not in
            (select team from standings_east
                where pts = (select max(pts)
                    from standings_east as t
                        where t.`div` = standings_east.`div`)))
        order by SortKey, pts desc, diff desc")

如果你访问我的网站，看看西部联盟（蓝旗）的积分榜，你会发现在“欧洲联盟”有3支球队得分相同（芝加哥，温尼伯，哥伦布）
mysql - NHL在SQL中的排名-LMLPHP
我希望查询只从该部门选择一个团队，根据谁拥有最多的“Wins/W”。
正确的排名应该是：
埃德蒙顿（西北）80
阿纳海姆（太平洋）74
哥伦布（CEN）71
达拉斯（PAC）73
芝加哥（CEN）71
温尼伯（CEN）71
我怎样才能做到这一点？

最佳答案

查询

select team,`div`,pts,1 as sortOrder
from
(   -- note use parentheses to avoid mysql error 1221
    (select team,`div`,pts,@cen:=team from `standings_west` where `div`='CEN' order by pts desc limit 1)
    union all
    (select team,`div`,pts,@pac:=team from `standings_west` where `div`='PAC' order by pts desc limit 1)
    union all
    (select team,`div`,pts,@nw:=team from `standings_west` where `div`='NW' order by pts desc limit 1)
) xDerived1
cross join (select @cen='',@pac='',@nw='') params
union
select team,`div`,pts,sortOrder
from
(   select team,`div`,pts,2 as sortOrder
    from `standings_west`
    where team!=@cen and team!=@pac and team!=@nw
    order by pts desc
    limit 3
) xDerived2
order by sortOrder,pts desc;

结果
+----------+-----+-----+-----------+
| team     | div | pts | sortOrder |
+----------+-----+-----+-----------+
| EDMONTON | NW  |  80 |         1 |
| ANAHEIM  | PAC |  74 |         1 |
| WINNIPEG | CEN |  71 |         1 |
| DALLAS   | PAC |  73 |         2 |
| CHICAGO  | CEN |  71 |         2 |
| COLUMBUS | CEN |  71 |         2 |
+----------+-----+-----+-----------+

存储过程
下面描述了一个存储过程，以便在遇到问题和需要时显示它。
drop procedure if exists xdoit;
delimiter $$
create procedure xdoit()
begin
select team,`div`,pts,1 as sortOrder
from
(   -- note use parentheses to avoid mysql error 1221
    (select team,`div`,pts,@cen:=team from `standings_west` where `div`='CEN' order by pts desc limit 1)
    union all
    (select team,`div`,pts,@pac:=team from `standings_west` where `div`='PAC' order by pts desc limit 1)
    union all
    (select team,`div`,pts,@nw:=team from `standings_west` where `div`='NW' order by pts desc limit 1)
) xDerived1
cross join (select @cen='',@pac='',@nw='') params
union
select team,`div`,pts,sortOrder
from
(   select team,`div`,pts,2 as sortOrder
    from `standings_west`
    where team!=@cen and team!=@pac and team!=@nw
    order by pts desc
    limit 3
) xDerived2
order by sortOrder,pts desc;
end$$
delimiter ;

调用存储过程
call xdoit();

这里有一些评论。
首先，sqlfiddle有来自西方的数据，但有来自东方的查询。根据表名，我建议您将所有数据放在一个表中，而不是两个表中，并有一列表示东或西。
查询使用交叉连接来建立变量来获取部门领导，因此对于sortOrder=2团队，这些部门领导被排除在外。
在@Clockwork的评论中对DIFF的实现进行必要的调整（即：得到71分的团队）
如果你有什么问题可以问。
下面是我根据您在聊天室中发表的评论为您提出的多查询php解决方案。
我唯一能想到的解决方案是，基于两个结果集的清晰结果，下面是。因此，由while驱动的next_result()循环。第一个结果集有前三行，第二个结果集有后十二行。这正是PHP看起来的样子。
还要注意，在PHP部分，由于我似乎在处理一个multi_查询，所以我利用了这个优势，传入mysql参数，而不是执行cross join来获取它们。
菲律宾比索
<!DOCTYPE html>
<html lang="en">
<head>
  <title>RGMG: Standings</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
  <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
</head>
<body>

<?php
    //mysqli_report(MYSQLI_REPORT_ALL);
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    error_reporting(E_ALL); // report all PHP errors
    ini_set("display_errors", 1);

    try {
        $mysqli= new mysqli('localhost', 'dbUser', 'thePassword', 'theDbName');

        echo "<table><div><br><br>";    // note you had this line a little bit wrong


        // notice below the concat of the $sql and the multi_query()
        $sql = "set @cen:='',@pac:='',@nw:=''; ";
        $sql .= "select *
                from
                (   select team,`div`,gp,win,lose,otl,goalsF,goalsA,diff,gpg,gaa,pts,1 as sortOrder
                    from
                    (   -- note use parentheses to avoid mysql error 1221
                        (select team,`div`,gp,win,lose,otl,goalsF,goalsA,diff,gpg,gaa,pts,@cen:=team from `standings_west` where `div`='CEN' order by pts desc, win desc, diff desc limit 1)
                        union all
                        (select team,`div`,gp,win,lose,otl,goalsF,goalsA,diff,gpg,gaa,pts,@pac:=team from `standings_west` where `div`='PAC' order by pts desc, win desc, diff desc limit 1)
                        union all
                        (select team,`div`,gp,win,lose,otl,goalsF,goalsA,diff,gpg,gaa,pts,@nw:=team from `standings_west` where `div`='NW' order by pts desc, win desc, diff desc limit 1)
                    ) xDerived1
                    union all
                    select team,`div`,gp,win,lose,otl,goalsF,goalsA,diff,gpg,gaa,pts,sortOrder
                    from
                    (   select team,`div`,gp,win,lose,otl,goalsF,goalsA,diff,gpg,gaa,pts,2 as sortOrder
                        from `standings_west`
                        where team!=@cen and team!=@pac and team!=@nw
                        order by pts desc
                    ) xDerived2
                ) xDerived3
                order by sortOrder,pts desc";

        echo "<div class='container'>";
        echo "<img src='http://i.imgur.com/sjDHhIV.png' width='100%' alt='West'>";
        echo "<table class='table table-condensed'>
        <tr class='top'>
        <th class='rank'></th>
        <th class='team'>TEAM</th>
        <th>DIV</th>
        <th>GP</th>
        <th>W</th>
        <th>L</th>
        <th class='otl'>OTL</th>
        <th class='pts'>PTS</th>
        <th>GF</th>
        <th>GA</th>
        <th>DIFF</th>
        <th>GPG</th>
        <th>GAA</th>
        ";
        $counter=1;
        $mysqli->multi_query($sql);
        while(true) {
            if ($result = $mysqli->store_result()) {
                while ($row = $result->fetch_assoc()) {
                    $gpg = ($row['goalsF']);
                    $gaa = ($row['goalsA']);

                    if ($row['gp'] != 0 ){
                        $gpg = ($row['goalsF'] / $row['gp']);
                        $gaa = ($row['goalsA'] / $row['gp']);
                    }
                    else {
                        $row['gp'] = "";
                    }

                    echo "<tr>
                        <td class='rank'>" . "$counter" . "</td>
                        <td class='team'>" . $row['team'] . "</td>
                        <td>" . $row['div'] . "</td>
                        <td>" . $row['gp'] . "</td>
                        <td>" . $row['win'] . "</td>
                        <td>" . $row['lose'] . "</td>
                        <td class='otl'>" . $row['otl'] . "</td>
                        <td class='pts'>" . $row['pts'] . "</td>
                        <td>" . $row['goalsF'] . "</td>
                        <td>" . $row['goalsA'] . "</td>
                        <td>" . $row['diff'] . "</td>
                        <td>" . round($gpg, 2) . "</td>
                        <td>" . round($gaa, 2) . "</td>";
                    $counter++;
                }
                $result->free();
            }
            if ($mysqli->more_results()) {
                $mysqli->next_result();
            }
            else {
                break;
            }
        }
        echo "</table></div>";

        $mysqli->close();
    } catch (mysqli_sql_exception $e) {
        throw $e;
    }

?>

</body>
</html>

mysql - NHL在SQL中的排名-LMLPHP

关于mysql - NHL在SQL中的排名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34459660/

10-11 05:35
Powered by LMLPHP ©2024 71 0.045009