基于条件第2部分的sql select min或max。

This post is in continuatation of a problem of another post sql select min or max based on condition

这篇文章是在继续一个问题的另一个后sql选择最小或最大值基于条件

I'm trying to get a row based on various conditions.

我试图根据不同的条件得到一排。

Scenario 1 - get highest row if no hours exist against it that has (setup + processtime > 0).

场景1 -如果没有小时，则获得最高行(setup + processtime >)。

Scenario 2 - if there's hours (like in this example) show next operation(oprnum) after this number. (which would be 60 in prodroute).

场景2——如果有小时(如本例中所示)，则显示此数字之后的下一个操作(oprnum)。(在普罗杜洛特60英镑)。

The query needs to work within a CTE as it's part of a bigger query.

查询需要在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)
;

expected output :

预期的输出:

ProdId  OprNum
12M0004893  60

ProdId  OprNum
12M0003571  70

6 个解决方案

#1

Based on new data and last comment on the answer by asker, here's the updated query and fiddle :http://sqlfiddle.com/#!6/87e2f/2

基于新的数据和asker的最后评论，这里是更新后的查询和fiddle:http://sqlfiddle.com/#

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!

嘿，我发现了一个不奏效的例子……orderID“12 m0003381”……我给你的小提琴添加了数据。我预计操作70将是最后一个设置或过程时间的操作……谢谢!

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

Explanation for below query changes:

以下查询变更说明:

The only change made to query was to handle the NULL value for weighted_value using the following syntax

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

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

The problematic part was the inner query which when run without group by clause shows what happened on a boundary case like added by user.

有问题的部分是内部查询，在没有group by子句的情况下运行时，它显示了在用户添加的边界情况下发生的情况。

( See fiddle for this here: http://sqlfiddle.com/#!6/87e2f/3 )

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

Without null handling, we had a NULL which after group by clause resulted in a structure like below

没有null处理，我们有一个null，后面的group by子句导致如下结构

( See fiddle for this here:http://sqlfiddle.com/#!6/87e2f/5 )

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

As you can see on grouping the LEAD value for prodid : 12M0003381, oprnum:70 resulted as NULL instead of 70 (as grouping 70 and NULL should give 70).

正如您在对prodid的铅值进行分组时看到的:12M0003381, oprnum:70的结果是NULL而不是70(因为分组70和NULL应该是70)。

This is justified if LEAD is calculated on grouped query/table , which is actually what is happening here.

如果在分组查询/表中计算铅，这是合理的，这实际上就是这里正在发生的事情。

In that case, the LEAD function will not return any data for the last row of partition. This is the boundary case and must be handled correctly with ISNULL.

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

I assumed that LEAD oprnum value of last row should be corrected as oprnum value of current row.

我假设最后一行的前置oprnum值应该作为当前行的oprnum值进行修正。

Old answer below:

旧的回答如下:

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

所以我尝试了，我发布了小提琴链接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

#2

This isn't the prettiest thing I have ever written but it works. I also tested it against the other fiddle with additional data.

这不是我写过的最漂亮的东西，但它确实有用。我还用其他数据测试了它。

Modified to meet new requirement.

修改以满足新的要求。

SELECT
    *
FROM
    (
        SELECT
            A.ProdID,
            MIN(A.OprNum) AS 'OprNum'
        FROM
            #ProdRoute AS A
            JOIN 
                (
                    SELECT
                        ProdID,
                        MAX(MaxOpCompleted) AS 'OprNum'
                    FROM
                        #ProdRouteTran
                    GROUP BY
                        ProdID
                ) AS B
                ON A.ProdId = B.ProdId AND A.OprNum > B.OprNum
        GROUP BY
            A.ProdID
    ) AS [HoursA]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
            DISTINCT
            A.ProdID,
            B.OprNum
        FROM
            #ProdRoute AS A
            JOIN 
                (
                    SELECT
                        ProdID,
                        MAX(MaxOpCompleted) AS 'OprNum'
                    FROM
                        #ProdRouteTran
                    GROUP BY
                        ProdID
                ) AS B
                ON A.ProdId = B.ProdId AND A.OprNum = B.OprNum
                    AND B.OprNum = (SELECT MAX(OprNum) FROM #ProdRoute WHERE ProdId = A.ProdId)
    ) AS [HoursB]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
            ProdId,
            MIN(OprNum) AS 'OprNum'
        FROM
            #ProdRoute
        WHERE
            ProdId NOT IN 
                (SELECT ProdId FROM #ProdRouteTran)
            AND (SetupTime <> 0 OR ProcessTime <> 0)
        GROUP BY
            ProdId
    ) AS [NoHoursA]
UNION ALL
SELECT
    *
FROM
    (
        SELECT
            ProdId,
            MIN(OprNum) AS 'OprNum'
        FROM
            #ProdRoute
        WHERE
            ProdId NOT IN 
                (SELECT ProdId FROM #ProdRouteTran)
        GROUP BY
            ProdId
        HAVING
            SUM(SetupTime) = 0 AND SUM(ProcessTime) = 0
    ) AS [NoHoursB]

#3

I'm not really sure I understand your question, but here is my attempt:

我不确定我是否理解你的问题，但这是我的尝试:

SELECT
    pr.ProdId,
    CASE 
        WHEN SUM(SetupTime) + SUM(ProcessTime) > 0 THEN MAX(x.OprNum)
        ELSE MAX(pr.OprNum)
    END
FROM ProdRoute pr
INNER JOIN (
    SELECT ProdID, MAX(MaxOpCompleted) AS OprNum
    FROM ProdRouteTran
    GROUP BY ProdID
)prt
    ON prt.ProdId = pr.ProdID
    AND prt.OprNum = pr.OprNum
OUTER APPLY(
    SELECT TOP 1 OprNum FROM ProdRoute 
    WHERE 
        ProdId = pr.ProdId
        AND OprNum > pr.OprNum
    ORDER BY OprNum
)x
GROUP BY pr.ProdId
ORDER BY pr.ProdId

#4

Try this -

试试这个,

-- display the next operation, if condition match
SELECT do_exists.ProdId, do_exists.OprNum
FROM ProdRoute pr
INNER JOIN ProdRouteTran prt 
    ON prt.ProdId = pr.ProdId 
    AND pr.OprNum = prt.MaxOpCompleted 
    AND (pr.SetupTime + pr.ProcessTime) > 0
OUTER APPLY (
    SELECT TOP(1) pr.* 
    FROM ProdRoute pr
    WHERE prt.ProdID = pr.ProdId
    AND   pr.OprNum > prt.MaxOpCompleted
    ORDER BY pr.OprNum
) do_exists


UNION ALL

-- display the max operation, if matching data is not found in ProdRoute. 
---- Matching Data not found - 1) There is entry in ProdRoute for particular ProdId but hours is not present
----                           2) There is no entry in ProdRoute for a particular ProdId    
SELECT pr.ProdId, MAX(pr.OprNum) OprNum
FROM ProdRoute pr
LEFT JOIN (
    SELECT pr.ProdId
    FROM ProdRoute pr
    INNER JOIN ProdRouteTran prt 
        ON prt.ProdId = pr.ProdId 
        AND pr.OprNum = prt.MaxOpCompleted 
        AND (pr.SetupTime + pr.ProcessTime) > 0
) pr_ex ON pr_ex.ProdId = pr.ProdId
WHERE pr_ex.ProdId IS NULL 
GROUP BY pr.ProdId

#5

I get the correct result with this SQL Fiddle.

我用这个SQL小提琴得到了正确的结果。

However I am not sure I fully understand the nothing >0 case. A little more data with all the cases could be usefull.

但是我不确定我是否完全理解>的情况。更多的数据和所有的案例都是有用的。

; With data as (
    Select r.ProdId
    , opr = case when h > 0 then  isnull(min(p.OprNum), r.OprNum) else  max(p.OprNum) end
    From (
        Select pr.ProdId, pr.OprNum, h = max(pr.SetupTime + pr.ProcessTime)     
        From ProdRoute as pr
        Inner Join ProdRouteTran as prt on pr.ProdId = prt.ProdID and pr.OprNum = prt.MaxOpCompleted
        Group By pr.ProdId, pr.OprNum
    ) as r
    left join ProdRoute as p on p.ProdId = r.ProdId and p.OprNum > r.OprNum
    Group By r.ProdId, r.OprNum, r.h
)
Select * From data

#6

I'm not entirely sure if I understood what you are trying to do, but could it be that this is the equivalent?

我不太确定我是否理解你的意图，但这是否等价?

SELECT
   t.ProdId,
   CASE WHEN r.OprNum IS NULL THEN t.MaxOpCompleted ELSE r.OprNum END AS OprNum
FROM
   ProdRouteTran t
   LEFT JOIN
      ProdRoute r
   ON
      r.ProdId = t.ProdId AND r.SetupTime + r.ProcessTime > 0 AND
      r.OprNum > t.MaxOpCompleted AND NOT EXISTS(
         SELECT * FROM ProdRoute p WHERE p.ProdId = t.ProdId AND
            p.OprNum > t.MaxOpCompleted AND p.OprNum < r.OprNum)

#1