sql 根据条件选择最小值或最大值第 2 部分

Question

这篇文章是另一篇文章问题的延续 sql select min or max based on condition

我正在尝试根据各种条件获取一行。

场景 1 - 如果不存在具有 (setup + processtime > 0) 的时间，则获取最高行。

场景 2 - 如果有几个小时(如本例所示)，则在此数字后显示下一个操作 (oprnum)。 (在 prodroute 中为 60)。

查询需要在 CTE 内工作，因为它是更大查询的一部分。

    CREATE TABLE ProdRoute
    ([ProdId] varchar(10), [OprNum] int, [SetupTime] int, [ProcessTime] numeric)
;

INSERT INTO ProdRoute
    ([ProdId], [OprNum], [SetupTime], [ProcessTime])
VALUES
    ('12M0004893', 12, 0.7700000000000000, 1.2500000000000000),
    ('12M0004893', 12, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 40, 0.0800000000000000, 0.4000000000000000),
    ('12M0004893', 50, 0.0400000000000000, 2.8000000000000000),
    ('12M0004893', 50, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 60, 0.0000000000000000, 0.6100000000000000),
    ('12M0004893', 60, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 70, 0.0000000000000000, 1.2900000000000000),
    ('12M0004893', 70, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 75, 0.0000000000000000, 3.8700000000000000),
    ('12M0004893', 75, 0.0000000000000000, 0.0000000000000000),
    ('12M0004893', 80, 0.0000000000000000, 0.5500000000000000),
('12M0003571', 3, 0.8900000000000000, 0.0000000000000000),
    ('12M0003571', 3, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 7, 1.0000000000000000, 0.0000000000000000),
    ('12M0003571', 10, 0.3000000000000000, 0.3000000000000000),
    ('12M0003571', 10, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 20, 0.0700000000000000, 0.1000000000000000),
    ('12M0003571', 20, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 30, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 40, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 50, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 60, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 60, 0.0000000000000000, 0.0000000000000000),
    ('12M0003571', 70, 0.0700000000000000, 0.1500000000000000),
    ('12M0003571', 70, 0.0000000000000000, 0.0000000000000000)
;

CREATE TABLE ProdRouteTran
    ([ProdID] varchar(10), [MaxOpCompleted] int, [Hours] numeric)
;

INSERT INTO ProdRouteTran
    ([ProdID], [MaxOpCompleted], [Hours])
VALUES
    ('12M0004893', 50, 1.7800000000000000),
('12M0003571', 70, 1.2660000000000000)
;

预期输出:

ProdId  OprNum
12M0004893  60

ProdId  OprNum
12M0003571  70

Answer 1

根据新数据和提问者对答案的最后评论，这里是更新的查询和 fiddle :http://sqlfiddle.com/#!6/87e2f/2

hey i found an example that doesn't work... orderID '12M0003381'... i've added data to your fiddle. I would expect to see operation 70 as that's the last operation with a setup or process time... thanks!

select prodid, ISNULL(MAX(weighted_value),MIN(oprnum)) as value from
(           
            select 
                a.prodid,
                a.oprnum,
                ISNULL(LEAD(a.oprnum,1) OVER(Partition by a.prodID  ORDER by a.oprnum asc),a.oprnum)  * 
                MAX(case 
                    when ISNULL([Hours], 0) >= (setupTime + ProcessTime) AND (SetupTime + ProcessTime ) > 0 
                    then 1
                    else NULL
                    end) as weighted_value
            from temp1 a LEFT JOIN temp4 b 
                    ON a.OprNum = b.OPRNUM
                    AND a.ProdID = b.ProdId
            group by a.prodid,a.oprnum
) t
group by prodid

以下查询更改的说明:

对查询所做的唯一更改是使用以下语法处理 weighted_value 的 NULL 值

ISNULL(LEAD(a.oprnum,1) OVER(Partition by a.prodID  ORDER by a.oprnum asc),a.oprnum)

有问题的部分是内部查询，当没有 group by 子句运行时，它会显示边界情况(例如用户添加的情况)上发生的情况。

(请参阅此处的 fiddle :http://sqlfiddle.com/#!6/87e2f/3)

如果没有 null 处理，我们有一个 NULL，在 group by 子句之后会产生如下所示的结构

(请参阅此处的 fiddle :http://sqlfiddle.com/#!6/87e2f/5)

正如您所看到的，对 prodid 的 LEAD 值进行分组:12M0003381，oprnum:70 结果为 NULL 而不是 70 (作为分组70 和 NULL 应该给出 70)。

如果 LEAD 是根据分组查询/表计算的，这是合理的，这实际上就是这里发生的情况。

在这种情况下，LEAD 函数将不会返回分区最后一行的任何数据。这是边界情况，必须使用 ISNULL 正确处理。

我假设最后一行的 LEAD oprnum 值应更正为当前行的 oprnum 值。

下面是旧答案:

So I tried and I am posting the fiddle link http://sqlfiddle.com/#!6/e965c/1

select prodid, ISNULL(MAX(weighted_value),MIN(oprnum)) as value from 
( 
select 
a.prodid, 
a.oprnum, 
LEAD(a.oprnum,1) OVER(Partition by a.prodID ORDER by a.oprnum asc) * 
MAX(case 
when ISNULL([Hours], 0) >= (setupTime + ProcessTime) AND (SetupTime + ProcessTime ) > 0 
then 1 
else NULL 
end) as weighted_value 
from ProdRoute a LEFT JOIN COMPLETED_OP b 
ON a.OprNum = b.OPRNUM 
AND a.ProdID = b.ProdId 
group by a.prodid,a.oprnum 
) t 
group by prodid