SQL:有多少行的最大值为给定列(来自一列列表)

Question

我刚刚开始学习SQL (特别是SQLite)，并且一直在使用this数据。

此列表中的每个漫威角色都有每个属性(力量、智能、速度等)的评级。我想找出有多少个字符的每个属性是最大的。例如，有多少角色的最大属性是力量？

我们可以假设，如果一个字符有两个或更多的属性作为其最高值，则该字符将计入每个属性。

我期望的输出是这样的：

best_Attribute      COUNT(*)
__________________________________
intelligence            a
strength                b
speed                   c
durability              d
energy_Projection       e
fighting_Skills         f

这是我的尝试：

SELECT COUNT(*),
    CASE
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == intelligence THEN "intelligence"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == strength THEN "strength"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == speed THEN "speed"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == durability THEN "durability"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == energy_Projection THEN "energy_Projection"
        WHEN MAX(intelligence, strength, speed, durability, energy_Projection, fighting_Skills) == fighting_Skills THEN "fighting_Skills"
    END as "best_Attribute"
FROM marvels
GROUP BY best_Attribute;

这是丑陋的，冗长的，甚至不能工作。下面是输出：

COUNT(*)    best_Attribute
__________________________
    2               4
    3               6
    7               7

有没有一种(很好的)方法来获得所需的输出？

Answer 1

我不确定您是否需要确切的建议输出。我可以提供以下查询，该查询将整个表的属性计数报告为单独的列。

SELECT
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = intelligence THEN 1 END) AS intelligence_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = strength THEN 1 END) AS strength_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = speed THEN 1 END) AS speed_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = durability THEN 1 END) AS durability_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = energy_Projection THEN 1 END) AS energy_Projection_count
    COUNT(CASE WHEN MAX(intelligence, strength, speed, durability, energy_Projection,
        fighting_Skills) = fighting_Skills THEN 1 END) AS fighting_Skills_count
FROM marvels;

请注意，如果您想要整个表的总计，则不需要在这里使用GROUP BY。

Answer 2

Tim的解决方案是性能最好的方法--以及您是如何处理问题的。但是，另一种方法是取消透视数据并使用聚合。我只是想提供一个替代方案，这样您就可以在SQL中看到解决问题的不同方法：

with a as (
      select name, 'intelligence' as attribute, intelligence as val
      from marvels
      union all
      select name, 'strength' as attribute, strength as val
      from marvels
      union all
      select name, 'speed' as attribute, speed as val
      from marvels
      union all
      select name, 'durability' as attribute, durability as val
      from marvels
      union all
      select name, 'energy_Projection' as attribute, energy_Projection as val
      from marvels
      union all
      select name, 'fighting_Skills' as attribute, fighting_Skills as val
      from marvels
     )
select a.attribute, count(*) as num_with_max
from a
where a.val = (select max(a2.val) from a a2 where a2.name = a.name)
group by a.attribute;