使用Max()函数选择组值 [英] Using Max() function to select group values
问题描述
我有这样一张表:
SKU项目价值
1503796 1851920 0,9770637
1503796 1636691 0,9747891
1503796 1503781 0,9741025
1503796 3205763 0,9741025
1503801 1999745 0,9776622
1503801 1999723 0,9718825
1503801 3651241 0 ,9348839
1503801 1773569 0,9331309
1503811 1439825 0,97053134
1503811 1636684 0,96297866
1503811 1636671 0,96003973
1503811 1600553 0,9535771
1503818 1636708 0,9440251
1503818 1636709 0,9440251
1503818 1779789 0,9423958
1503818 3322310 0,9369579
我需要像这样得到输出(按最大值分组):
SKU项目价值
1503796 1851920 0,9770637
1503801 1999745 0,9776622
1503811 1439825 0,97053134
1503818 1636708 0,9440251
试图使用这样的smth:
从导入
中选择SKU,ITEM,VALUE,其中value =(从导入中选择max(value))
但它只选择一个最大值的行。如何重写查询?
解决方案使用ROW_NUMBER排序记录,以便sku的最大值为#1。然后只保留那些排在#1的记录。
选择sku,项目,值
from
(
选择
mytable。*
row_number()over(由sku按值desc分区)作为
from mytable
其中rn = 1;
对于SKU 1503818,您将获得以下两种中的任意一种:
1503818 1636708 0,9440251
1503818 1636709 0,9440251
如果您想要一个特定的(例如具有更高的商品编号的商品),然后将此条件添加到Row_Number的ORDER BY子句中。
对于您自己试过的查询:您应该而不是寻找sku-value对:
从import $ b $ select中选择SKU,ITEM,VALUE其中(sku,value) in(从sku导入组中选择sku,max(value));
如果与SKU 1503818相同,则此查询会为您提供两条记录。
I've got a table like this:
SKU ITEM VALUE 1503796 1851920 0,9770637 1503796 1636691 0,9747891 1503796 1503781 0,9741025 1503796 3205763 0,9741025 1503801 1999745 0,9776622 1503801 1999723 0,9718825 1503801 3651241 0,9348839 1503801 1773569 0,9331309 1503811 1439825 0,97053134 1503811 1636684 0,96297866 1503811 1636671 0,96003973 1503811 1600553 0,9535771 1503818 1636708 0,9440251 1503818 1636709 0,9440251 1503818 1779789 0,9423958 1503818 3322310 0,9369579
I need to get output like this (grouped with max value):
SKU ITEM VALUE 1503796 1851920 0,9770637 1503801 1999745 0,9776622 1503811 1439825 0,97053134 1503818 1636708 0,9440251
tried to use smth like this:
select SKU, ITEM, VALUE from import where value=(select max(value) from import )
But it select only one row with max value. How to rewrite query?
解决方案Rank the records with ROW_NUMBER, so that the max value for an sku gets #1. Then keep only those records ranked #1.
select sku, item, value from ( select mytable.* row_number() over (partition by sku order by value desc) as rn from mytable ) where rn = 1;
For SKU 1503818 you will get either of these two:
1503818 1636708 0,9440251 1503818 1636709 0,9440251If you want a particular one (e.g. the one with the higher item number) then add this criteria to Row_Number's ORDER BY clause.
As to the query you tried yourself: You should be looking for sku-value pairs instead:
select SKU, ITEM, VALUE from import where (sku,value) in (select sku, max(value) from import group by sku);
In case of a tie, as with SKU 1503818, this query will get you both records, however.
这篇关于使用Max()函数选择组值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!