大家好,我正在尝试找到一种根据以下条件从一系列数据中选择最小值或最大值的方法:
- 如果 setuptime 和 processtime cols 均为 0,则选择 MIN(oprNum)
(操作尚未开始,因此获取第一个 oprnum)
- 如果 setuptime 和 process time 不为 0,则获取 max oprnum(活动操作)。
基于其中任何一个,我想要一行......请参阅附加的数据示例。谢谢!这是一个更大的查询的一部分,所以我需要每个 prodid 1 个输出行......
+------------+--------+---------+--------------------+--------------------+
| 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) 当我想要最后一行时我得到这一行
具有设置时间或处理时间
该查询使用的逻辑是简单地计算附加列weighted_value
。在外部查询中,我们像以前一样使用最小超过值和最大超过加权值。
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
更新的小提琴链接:http://sqlfiddle.com/#!6/b7ecb/20 http://sqlfiddle.com/#!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 小提琴链接http://sqlfiddle.com/#!6/c52e22/1 http://sqlfiddle.com/#!6/c52e22/1
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)