在下面的代码中,我从排名表中添加了Fanbase、coach、TeamRanks、组织和Kicker。它返回每行的总和作为合计。但是,我不确定如何显示合计的最大值。我不需要完整的加法列表。
Select
ID,
(Fanbase + Coach + TeamRanks + Organization + Kicker) as total,
Fanbase,
Coach,
TeamRanks,
Organization,
Kicker
from Ranks;
这将返回每一列的总数,但我需要max。合计的值。
Team Fanbase Coach TeamRanks Organization Kicker
Chargers 80 60 29 18 10
Patriots 100 100 32 32 20
RAMS 80 90 30 25 23
发布于 2019-04-25 09:32:28
最快的方法是使用ORDER BY DESC
和LIMIT
(而且不需要从原始查询派生太多内容)。
-- Assuming that ID=teamname
SELECT ID FROM
(SELECT
ID,
(Fanbase + Coach + TeamRanks + Organization + Kicker) AS total
FROM Ranks ORDER BY total DESC LIMIT 1) r;
更新:我脑海中有一个查询,但我担心它在某些MySQL版本中可能不起作用,但刚才我有时间尝试一下,它似乎在MySQL 5.5,5.6,5.7和8.0上工作得很好。此查询要短得多,并且不使用子查询:
SELECT ID
FROM ranks
ORDER BY (Fanbase + Coach + TeamRanks + Organization + Kicker) DESC
LIMIT 1;
另外,我想指出的是,如果其中一列包含所有团队的空值;例如,如果所有Kicker
值都为空值,则此查询似乎不起作用。我已经检查了数学公式,如果(Fanbase + Coach + TeamRanks + Organization + Kicker) = (10+ 10+ 20+ 30+ NULL),它将返回NULL而不是70。你可以添加像IFNULL(field,0)
这样的函数。比如;
SELECT ID
FROM ranks
ORDER BY (IFNULL(Fanbase,0)+IFNULL(Coach,0)+IFNULL(TeamRanks,0)+IFNULL(Organization,0)+IFNULL(Kicker,0)) DESC
LIMIT 1;
但是,最好的方法当然是将字段设置为默认的0
和NOT NULL
。在这里我只关心What if ... ?
发布于 2019-04-23 03:20:32
使用MAX()
函数聚合列。例如:
Select
MAX(Fanbase + Coach + TeamRanks + Organization + Kicker) as max_total
from Ranks;
编辑:要获取具有max_total
值的团队的数据,您可以这样做:
Select
ID,
(Fanbase + Coach + TeamRanks + Organization + Kicker) as total,
Fanbase,
Coach,
TeamRanks,
Organization,
Kicker
from Ranks
where Fanbase + Coach + TeamRanks + Organization + Kicker = (
Select
MAX(Fanbase + Coach + TeamRanks + Organization + Kicker) as max_total
from Ranks
)
这个查询将返回第一个位置的所有团队,如果有多个团队的话。
https://stackoverflow.com/questions/55799891
复制相似问题