sql - 找到 6 个月的差距并抓住一切 之后，如果没有 6 个月的差距，那么就抓住一切

Question

我添加了一张我遇到的图片。突出显示的是我需要抓取的那些，而带有删除线的是我不需要的。所以第一个 tempid (561346) 没有 6 个月的差距，所以我想要所有这些行。第二个 Tempid(661450) 在第 9 行有 6 个月的差距，所以我只想要下一个（第 10 行）其余行用于我不想要的那个 tempid。Last tempid(662020) 在第 16 行有 8 个月的间隔，然后还有 4 行。我只想抓住那 4 行。我希望这是有道理的。

我只是需要有人帮助我。几天来，我一直试图弄清楚这一点。

  WITH cte AS 
  ( 
  SELECT pt.TempID, MIN(pt.InvoiceDate) AS 'min', MAX(pt.InvoiceDate) AS 
  'Max', 
  pt.JobNumber , 
  ROW_NUMBER() OVER (PARTITION BY tempid ORDER BY MAX(pt.InvoiceDate)) AS 
  'Row'  
  FROM #temp t 
  JOIN pamwebhouse..pamtempjobs pt 
  ON SUBSTRING(t.EMPLOYEE, 5,10) = pt.TempID 
  WHERE 
  pt.Status <> 'Posted'
  AND pt.PayTypeID IN ('Regul','OT','Sick') 
  GROUP BY pt.TempID, pt.jobnumber
  ) 
  SELECT a.TempID, a.JobNumber,DATEDIFF(month, a.max, a.start) AS 'Diff' , 
  A.MAX  
  , a.Row  
  INTO #temp2 
  FROM ( 
  SELECT c.TempID, c.JobNumber, c.min AS 'Min', c.max AS 'Max', 
  CASE WHEN c2.TempID IS NULL THEN NULL ELSE c2.min END AS 'Start', c.Row
  FROM cte c
   LEFT JOIN cte c2 
   ON c2.row = c.Row +1
   AND c.TempID = c2.TempID
   --ORDER BY c.TempID
   ) a 

   ORDER BY a.TempID


    --This is what is giving me the output in the screenshot. 
    select t.tempid, t.JobNumber, test AS 'Gap',row,
             max(case when test >= 6 then jobnumber  END) over (PARTITION BY 
    tempid ORDER by max) as id_6
      from #temp2 t 

标签： sqlsql-server

Answer 1

您可以使用窗口功能：

select t.*
from (select t.*,
             max(case when gap = 6 then row end) over (partition by tempid order by row) as seq
      from table t
     ) t
where (t.gap <> 6 and t.seq is not null);