个人中心
gpt4 book ai didi

mysql - SQL 中的 NHL 排名

转载 作者：行者123 更新时间：2023-11-29 01:53:45 25 4
gpt4 key购买 nike

我有一个 SQL 查询，它根据旧的 NHL 格式生成球队排名。

代码的第一部分获取每个分区的前 3 支球队，第二部分获取其余球队，按点/差异对它们进行排序。

这可以在这里看到: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")

如果您访问我的网站并查看西部联盟的排名(蓝色横幅)，您会注意到“CEN”中有 3 支球队的积分相同(芝加哥、温尼伯、哥伦布)

standings

我希望查询根据“Wins/W”最多的人从该部门中仅选择一个团队。

正确的排名应该是:

埃德蒙顿(西北)80
阿纳海姆 (PAC) 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 的评论中，根据需要调整决胜局(即:获得 71 分的团队)关于您实现 DIFF 的情况

有问题就问。

以下是我根据您在该聊天室中发表的评论为您提出的 multi_query php 解决方案。

我能想出的唯一解决方案是基于两个结果集中明显返回的结果。因此，while 循环由 next_result() 驱动。第一个结果集有前 3 行，第二个结果集有后面的十二行。这正是 PHP 的看法。

另请注意，在 PHP 部分，由于我似乎在处理多查询，因此我利用了它并传递了 mysql 参数，而不是通过cross join 来获取它们。

PHP

<!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>

enter image description here

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

25 4 0
文章推荐： ios - 如何访问另一个类中的变量并更改其值
文章推荐： ios - 我已经在自定义 UITableViewCell 类中覆盖了 setHighlight 。但是，我并不总是想使用它。我怎样才能防止它被使用？
文章推荐： mysql - 从表中选择相等或最接近的更大数字？
文章推荐： ios - Phonegap ios 应用程序因启动时崩溃而被拒绝
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com