获取具有MIN / MAX值的行的其他字段值

Here is a sample of my data and what I would like to see:

以下是我的数据示例以及我希望看到的内容:

JOB    OPSEQ    OPCOMPLETE   OPCODE

100     1        yes          pull  
100     2        yes          weld  
100     3        no           grind    
100     4        no           machine  
100     5        no           asmbl

So I want to select the min(opseq) where opcomplete=no and the max(opseq) where opcomplete=yes, as well as the opcode of the min and max opseq's. In this example that would be:
min(opseq):3
opcode of min op: grind
max(opseq): 2 opcode of max op: weld

所以我想选择min(opseq),其中opcomplete = no和max(opseq),其中opcomplete = yes,以及min和max opseq的操作码。在这个例子中,min:op(opseq):3 op op的操作码:grind max(opseq):2操作码max op:weld

The reason I am looking for this is to get the opcode of the smallest opseq that is NOT complete.
I got the min and max opseqs to work great. This is what I had:

我正在寻找这个的原因是获得最不完整的opseq的操作码。我让min和max opseqs工作得很好。这就是我所拥有的:

(SELECT   company, jobnum, MAX(oprseq) AS maxclosed, opcomplete
 FROM     joboper AS joboper_2
 WHERE    (company = 'lot') AND (opcomplete = '1')
 GROUP BY company, jobnum, opcomplete) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN
(SELECT   company, jobnum, MIN(oprseq) AS minopen, opcomplete
 FROM     joboper AS joboper_1
 WHERE    (company = 'lot') AND (opcomplete = '0')
 GROUP BY company, jobnum, opcomplete) AS t_joboper2 ON joboper.company = t_joboper2.company AND joboper.jobnum = t_joboper2.jobnum

So when I tried to get my opcodes added in, it didn't work, and I started gettings all kinds of duplicate values. This is what I wrote:

因此,当我尝试添加我的操作码时,它不起作用,我开始获得各种重复值。这就是我写的:

(SELECT   company, jobnum, MAX(oprseq) AS maxclosed, opcomplete, opcode as maxopcode
 FROM     joboper AS joboper_2
 WHERE    (company = 'lot') AND (opcomplete = '1')
 GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN
(SELECT   company, jobnum, MIN(oprseq) AS minopen, opcomplete, opcode as minopcode
 FROM     joboper AS joboper_1
 WHERE    (company = 'lot') AND (opcomplete = '0')
 GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper2 ON joboper.company = t_joboper2.company AND joboper.jobnum = t_joboper2.jobnum

Here is my entire code as it stands at the moment (with the opcodes pulling in all the duplicate values:

这是我现在的整个代码(使用操作码拉入所有重复的值:

SELECT     jobhead.company, jobhead.jobnum, jobhead.partnum, 
           jobhead.partdescription, jobhead.startdate, 
           jobhead.reqduedate, jobhead.prodqty, jobhead.qtycompleted,
           joboper.oprseq, joboper.opcode, joboper.opcomplete, 
           joboper.qtycompleted AS joboperqtycomplete, 
           resourcegroup.description AS rgroupdescription,
           dmrhead.dmrnum, poheader.ponum, vendor.name AS vendorname,
           t_joboper2.minopen AS minopen, t_joboper2.minopcode AS minopcode,
           t_joboper1.maxclosed AS maxclosed, t_joboper1.maxopcode AS maxopcode
FROM       jobhead LEFT OUTER JOIN
           joboper INNER JOIN
           (SELECT   company, jobnum, MAX(oprseq) AS maxclosed, 
                     opcomplete, opcode as maxopcode
            FROM     joboper AS joboper_2
            WHERE    (company = 'lot') AND (opcomplete = '1')
            GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper1 
              ON     joboper.company = t_joboper1.company 
            AND      joboper.jobnum = t_joboper1.jobnum INNER JOIN
           (SELECT   company, jobnum, MIN(oprseq) AS minopen, opcomplete, 
                     opcode as minopcode
            FROM     joboper AS joboper_1
            WHERE    (company = 'lot') AND (opcomplete = '0')
            GROUP BY company, jobnum, opcomplete, opcode) AS t_joboper2 
              ON     joboper.company = t_joboper2.company
            AND      joboper.jobnum = t_joboper2.jobnum 
              ON     jobhead.company = joboper.company
            AND      jobhead.jobnum = joboper.jobnum LEFT OUTER JOIN
                     resourcegroup ON joboper.company = resourcegroup.company
            AND      joboper.opcode = resourcegroup.opcode LEFT OUTER JOIN
                     dmrhead ON joboper.company = dmrhead.company
            AND      joboper.jobnum = dmrhead.jobnum
            AND      joboper.assemblyseq = dmrhead.assemblyseq 
            AND      joboper.oprseq = dmrhead.oprseq LEFT OUTER JOIN
                     porel ON joboper.company = porel.company
            AND      joboper.jobnum = porel.jobnum
            AND      joboper.assemblyseq = porel.assemblyseq
            AND      joboper.oprseq = porel.jobseq LEFT OUTER JOIN
                     podetail ON porel.company = podetail.company
            AND      porel.ponum = podetail.ponum 
            AND      porel.poline = podetail.poline LEFT OUTER JOIN
                     poheader ON podetail.company = poheader.company 
            AND      podetail.ponum = poheader.ponum LEFT OUTER JOIN
                     vendor ON poheader.company = vendor.company
            AND      poheader.vendornum = vendor.vendornum  
WHERE     (jobhead.jobreleased = 1)
 AND      (jobhead.jobcomplete = 0) 
 AND      (jobhead.company = 'lot') 
 AND      (jobhead.plant = '001')

I hope this all makes sense what I am trying to do here. If it isn't completely obvious this is my first time asking a question on here. In advance I appreciate all the help!!!

我希望这一切都有意义我想在这里做什么。如果不是很明显,这是我第一次在这里问一个问题。提前我感谢所有的帮助!

New - 12-21-12

新的 - 12-21-12

Thank you both for your help! I tried both of your suggestions, but couldn't get either one to get the exact results I wanted. But each answer helped me get to what it eventually took to get what I needed. Since this is my first question, I don't know what I should do as far as marking which answer as a solution? Like I said, both answers helped me out a lot, and I think that the reason I couldn't get the results I wanted out of them is soley my fault. After working on this more, I realized that even though I tried very hard to be clear in my question, as I go back I see how I could have worded things much better. Again, I appreciate all the help very much, and I look forward to asking better questions in the future!
By the way, this is the code that worked in the end.

谢谢你们的帮助!我尝试了你的两个建议,但无法得到任何一个得到我想要的确切结果。但每个答案都帮助我了解它最终需要的东西。由于这是我的第一个问题,我不知道应该做些什么来标记哪个答案作为解决方案?就像我说的那样,这两个答案对我帮助很大,我认为我无法得到他想要的结果的原因是我的错。在进行了更多的工作后,我意识到即使我在我的问题中努力清楚,但是当我回去时,我看到我可以更好地措辞。再次,我非常感谢所有的帮助,我期待将来提出更好的问题!顺便说一下,这是最终有效的代码。

 SELECT     jobhead.company, jobhead.jobnum, jobhead.partnum, jobhead.partdescription, jobhead.startdate, jobhead.reqduedate, jobhead.prodqty, jobhead.qtycompleted, 
                  joboper.oprseq, joboper.opcode, joboper.opcomplete, joboper.qtycompleted AS joboperqtycomplete, resourcegroup.description AS rgroupdescription, 
                  dmrhead.dmrnum, poheader.ponum, vendor.name AS vendorname, t_joboper2.minopen, t_joboper1.maxclosed, t_joboper3.opcode AS minopcode
FROM         jobhead LEFT OUTER JOIN
                  joboper INNER JOIN
                      (SELECT     company, jobnum, MAX(oprseq) AS maxclosed, opcomplete
                        FROM          joboper AS joboper_1
                        WHERE      (company = 'lot') AND (opcomplete = '1')
                        GROUP BY company, jobnum, opcomplete) AS t_joboper1 ON joboper.company = t_joboper1.company AND joboper.jobnum = t_joboper1.jobnum INNER JOIN
                      (SELECT     company, jobnum, MIN(oprseq) AS minopen, opcomplete
                        FROM          joboper AS joboper_2
                        WHERE      (company = 'lot') AND (opcomplete = '0')
                        GROUP BY company, jobnum, opcomplete) AS t_joboper2 ON joboper.company = t_joboper2.company AND 
                  joboper.jobnum = t_joboper2.jobnum INNER JOIN
                      (SELECT     company, jobnum, oprseq, opcomplete, opcode
                        FROM          joboper AS joboper_3
                        WHERE      (company = 'lot') AND (opcomplete = '0'))
                         AS t_joboper3 ON t_joboper2.company = t_joboper3.company AND t_joboper2.jobnum = t_joboper3.jobnum AND 
                  t_joboper2.minopen = t_joboper3.oprseq ON jobhead.company = joboper.company AND jobhead.jobnum = joboper.jobnum LEFT OUTER JOIN
                  resourcegroup ON joboper.company = resourcegroup.company AND joboper.opcode = resourcegroup.opcode LEFT OUTER JOIN
                  dmrhead ON joboper.company = dmrhead.company AND joboper.jobnum = dmrhead.jobnum AND joboper.assemblyseq = dmrhead.assemblyseq AND 
                  joboper.oprseq = dmrhead.oprseq LEFT OUTER JOIN
                  porel ON joboper.company = porel.company AND joboper.jobnum = porel.jobnum AND joboper.assemblyseq = porel.assemblyseq AND 
                  joboper.oprseq = porel.jobseq LEFT OUTER JOIN
                  podetail ON porel.company = podetail.company AND porel.ponum = podetail.ponum AND porel.poline = podetail.poline LEFT OUTER JOIN
                  poheader ON podetail.company = poheader.company AND podetail.ponum = poheader.ponum LEFT OUTER JOIN
                  vendor ON poheader.company = vendor.company AND poheader.vendornum = vendor.vendornum
WHERE     (jobhead.jobreleased = 1) AND (jobhead.jobcomplete = 0) AND (jobhead.company = 'lot') AND (jobhead.plant = '001')

3 个解决方案

#1

Here is one approach:

这是一种方法:

select JOB,
       min(case when OPCOMPLETE = 'no' then OPSEQ end) as MIN_NO_OPSEQ,
       min(case when OPCOMPLETE = 'no' then OPCODE end) as MIN_NO_OPCODE,
       min(case when OPCOMPLETE = 'yes' then OPSEQ end) as MAX_YES_OPSEQ,
       min(case when OPCOMPLETE = 'yes' then OPCODE end) as MAX_YES_OPCODE
  from ( select JOB,
                OPSEQ,
                OPCOMPLETE,
                OPCODE,
                rank() over (partition by JOB, OPCOMPLETE order by OPSEQ asc) as R_NO,
                rank() over (partition by JOB, OPCOMPLETE order by OPSEQ desc) as R_YES
           from TABLE_NAME
       )
 where OPCOMPLETE = 'no' and R_NO = 1    -- row with min(OPSEQ) where OPCOMPLETE = 'no'
    or OPCOMPLETE = 'yes' and R_YES = 1  -- row with max(OPSEQ) where OPCOMPLETE = 'yes'
 group
    by JOB
;

Notes:

not tested.
in lines 2–5, each min can be changed to max with no effect, since only one row will meet all the necessary criteria. The min (or max) is only necessary because of the group by: we're combining two rows into one by selecting the non-null value.

在第2-5行中,每个min可以更改为max而不起作用,因为只有一行将满足所有必要的标准。 min(或max)仅是必需的,因为group by:我们通过选择非null值将两行合并为一。

for information on rank(), see its documentation on MSDN.

有关rank()的信息,请参阅MSDN上的文档。

#2

I think this is a lot easier than you are making it, as long as OPSEQ is a unique value:

我认为这比你制作它容易得多,只要OPSEQ是一个独特的价值:

select
    opseq, opcode
from
    joboper
where
    opseq in (select min(opseq) from joboper where opcomplete = 'no')
    or
    opseq in (select max(opseq) from joboper where opcomplete = 'yes')

#3

Assuming you are using SQL Server 2005 or later, you could also consider using CROSS APPLY, like this:

假设您使用的是SQL Server 2005或更高版本,您还可以考虑使用CROSS APPLY,如下所示:

SELECT
  j.JOB,
  y.OPSEQ  AS LastCompleteOPSEQ,
  y.OPCODE AS LastCompleteOPCODE,
  n.OPSEQ  AS FirstIncompleteOPSEQ,
  n.OPCODE AS FirstIncompleteOPCODE
FROM (SELECT DISTINCT JOB FROM joboper) j
CROSS APPLY (
  SELECT TOP 1 OPSEQ, OPCODE
  FROM joboper
  WHERE JOB = j.JOB AND OPCOMPLETE = 'yes'
  ORDER BY OPSEQ DESC
) y
CROSS APPLY (
  SELECT TOP 1 OPSEQ, OPCODE
  FROM joboper
  WHERE JOB = j.JOB AND OPCOMPLETE = 'no'
  ORDER BY OPSEQ ASC
) n
;

You can this query it at SQL Fiddle.

你可以在SQL Fiddle查询它。

#1