sql根据条件选择最小值或最大值

本文介绍了sql根据条件选择最小值或最大值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好，我正在尝试找到一种方法，根据这些条件从一系列数据中选择最小值或最大值:

Hi all I'm trying to find a way to select min or max from a range of data based on these conditions:

• 如果 setuptime 和 processtime cols 都是 0 选择 MIN(oprNum)(操作还没有开始，所以先获取 oprnum)
• 如果 setuptime 和 process time 不为 0，则获取 max oprnum(活动操作).

基于其中任何一个，我想要一行...请参阅附加的数据示例.谢谢！这是一个更大的查询的一部分，所以我需要每个 prodid 1 个输出行...

Based on either of these I want ONE row... Please see attached example of data. Thanks! This is part of a much larger query so i need 1 output row per prodid...

+------------+--------+---------+--------------------+--------------------+ | ProdId | OprNum | Company | SetupTime | ProcessTime | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 10 | 12 | 1.3400000000000000 | 1.6100000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 10 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 15 | 12 | 1.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 50 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 60 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 60 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 70 | 12 | 0.0700000000000000 | 0.0400000000000000 | +------------+--------+---------+--------------------+--------------------+ | 12M0003381 | 70 | 12 | 0.0000000000000000 | 0.0000000000000000 | +------------+--------+---------+--------------------+--------------------+

推荐答案

更新的查询:基于最后的评论

如果最大记录有 0 次，我想选择最后一条记录有设置时间或处理时间.如果我将此行添加到您的小提琴('12M0003381',80,12,0.00,0.00) 当我想要最后一个时，我得到这一行带有设置时间或处理时间

if the max record has 0 times, i want to select the last record that has a setuptime or process time. If I add this row to your fiddle ('12M0003381',80,12,0.00,0.00) I get this row when i want the last one with a setuptime or process time

此查询使用的逻辑是简单地计算附加列weighted_value.在外部查询中，我们像以前一样使用 min over value 和 max over weighted value.

logic used by this query is to simply calculate an additional column weighted_value. In outer query we use min over value and max over weighted value like before.

select t.prodId, case when MAX(t.setuptime+ t.processtime)>0 then MAX(t.weighted_value) else MIN(t._value) end as value from ( select prodID, oprnum as _value, setuptime, processtime, case when setuptime+processtime>0 then oprnum else NULL end as weighted_value from tbl ) t group by t.prodID

更新小提琴链接:sqlfiddle/#!6/b7ecb/20

请试试这个查询

select t1.ProdId, case when exists( select 1 from tbl t2 where t2.setuptime >0 or t2.Processtime>0 and t2.prodId=t1.prodId ) then MAX(t1.oprNum) ELSE MIN(t1.oprNum) END from tbl t1 group by ProdId

sql 小提琴链接 sqlfiddle/#!6/c52e22/1