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

问题描述

我正在尝试解决 https://www.hackerrank.com/challenges/harry-potter-and-wands/problem

使用正确的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_need所需的行提出建议，以便可以将其进一步使用.

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

PS:我已经用示例数据此处

PS: I have created a sqlfiddle with sample data here

推荐答案

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

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

第1步:

Step 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步:

Step 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步:

Step 3:

最后一步是将wands_property与步骤2中获得的新表连接起来，并根据问题的需要对其进行排序

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在hackerrank上的库存问题]替代分组中的部分(非聚合列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！