sql - 选择所有记录的列最大值和最小值

我有一张桌子，如下:我想获得具有最大值和最小值的列名称，但所有记录的总体列(当然它将具有最大值)除外。

State   Population   age_below_18  age_18_to_50   age_50_above
 1         1000          250          600            150
 2         4200          400          300            3500

结果:

   State    Population   Maximum_group    Minimum_group    Max_value   Min_value
     1         1000      age_18_to_50      age_50_above      600         150
     2         4200      age_50_above      age_18_to_50     3500         300

最佳答案

假设所有值都不是NULL，则可以使用greatest()和least():

select state, population,
       (case when age_below_18 = greatest(age_below_18, age_18_to_50, age_50_above)
             then 'age_below_18'
             when age_below_18 = greatest(age_below_18, age_18_to_50, age_50_above)
             then 'age_18_to_50'
             when age_below_18 = greatest(age_below_18, age_18_to_50, age_50_above)
             then 'age_50_above'
         end) as maximum_group,
       (case when age_below_18 = least(age_below_18, age_18_to_50, age_50_above)
             then 'age_below_18'
             when age_below_18 = least(age_below_18, age_18_to_50, age_50_above)
             then 'age_18_to_50'
             when age_below_18 = least(age_below_18, age_18_to_50, age_50_above)
             then 'age_50_above'
         end) as minimum_group,
        greatest(age_below_18, age_18_to_50, age_50_above) as maximum_value,
        least(age_below_18, age_18_to_50, age_50_above) as minimum_value
from t;

如果结果集实际上是由查询生成的，则可能有更好的方法。

另一种方法是“取消透视”数据，然后重新聚合:

select state, population,
       max(which) over (dense_rank first_value order by val desc) as maximum_group,
       max(which) over (dense_rank first_value order by val asc) as minimum_group,
       max(val) as maximum_value,
       min(val) as minimum_value
from ((select state, population, 'age_below_18' as which, age_below_18 as val
       from t
      ) union all
      (select state, population, 'age_18_to_50' as which, age_18_to_50 as val
       from t
      ) union all
      (select state, population, 'age_50_above' as which, age_50_above as val
       from t
      )
     ) t
group by state, population;

尽管随着值数量的增加，此方法可能更容易实现，但它的性能将低于第一种方法。但是，Oracle 12C支持横向联接，在这种情况下，类似的方法将具有竞争优势。

关于sql - 选择所有记录的列最大值和最小值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50132800/