通过 [Ollivander's Inventory problem onhackerrank] 在组中替代部分(非聚合列) [英] Alternative to partial(nonaggregated column) in group by [Ollivander's Inventory problem on hackerrank]

问题描述

我正在尝试解决 https://www.hackerrank.com/challenges/哈利波特与魔杖/问题

通过适当的 mysql 设置，您可以执行以下操作

With proper mysql setup one can do following

select w.id, wp.age, min(w.coins_needed), w.power from wands w
join wands_property wp
on wp.code = w.code and wp.is_evil=0
group by w.code
order by w.power desc, wp.age desc;

但是hackerrank 的mysql 设置不允许部分分组.它抛出错误

But hackerrank's mysql setup does not allow partial grouping. It throws error

SELECT 列表不在 GROUP BY 子句中并且包含非聚合run_eootvjd0lna.w.id"列在功能上不依赖GROUP BY 子句中的列；这与sql_mode=only_full_group_by

SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_eootvjd0lna.w.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

所以我想首先过滤掉wands表中给定代码的所有重复项，只留下具有最​​少coins_needed的行.这样我就可以在提取的表上加入 wands_property.

So I would like to first filter out all the duplicates for a given code in wands table and only leave rows having minimum coins_needed. This way I can then join wands_property on the extracted table.

想获得有关仅提取相关最小coins_needed行的建议，以便进一步使用.

Would like to get suggestion on extraction of only relevant minimum coins_needed rows, so that it can be used further on.

PS:我用示例数据创建了一个 sqlfiddle 这里

PS: I have created a sqlfiddle with sample data here

推荐答案

这是我对解决方案的说明(步骤 3 中的最终解决方案):

Here is my Explaination of the solution (final solution in step 3):

第 1 步:

根据功率和所需的最少硬币过滤行代码.为什么编码 &power 因为需求希望我们根据年龄和权力进行过滤，而年龄与代码有 1-1 关系，因此代码和权力是过滤的逻辑位置

Filter rows with minimum coins needed based on power & code. Why code & power because requirement wants us to filter based on age and power and age has 1-1 relationship with code so code and power are logical places to filter

select w2.code, w2.power, min(w2.coins_needed) coins
from wands w2
group by w2.code, w2.power

第 2 步:

使用此表仅过滤行以获取魔杖表的所有列

Use this table with only filtered rows to get all the columns of wands table

select w.id, wp.age, w.code, min(w.coins_needed), w.power  from wands w
join (
        select w2.code, w2.power, min(w2.coins_needed) coins from wands w2
        group by w2.code, w2.power
      ) wcp
on wcp.code = w.code and wcp.power = w.power and wcp.coins = w.coins_needed

第 3 步:

最后一步是将我们在第 2 步中得到的新表与 wands_property 连接起来，并根据问题的要求对其进行排序

Final step is to join with wands_property with the new table we got in step 2 and order it as required by the problem

select w.id, wp.age, w.code, min(w.coins_needed), w.power  from wands w
join (
        select w2.code, w2.power, min(w2.coins_needed) coins from wands w2
        group by w2.code, w2.power
      ) wcp
on wcp.code = w.code and wcp.power = w.power and wcp.coins = w.coins_needed
join wands_property wp
on wp.code = w.code and wp.is_evil=0
order by w.power desc, wp.age desc ;

这篇关于通过 [Ollivander's Inventory problem onhackerrank] 在组中替代部分(非聚合列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！