事件驱动select语句

Question

我有一个事件驱动的mssql数据库，它有以下列。

id    job_id    request    response   status    date_time  
-----------------------------------------------------------
 1      15     <message>    null     pending   22-09-2017 08:00:00
 2      15     null       <message>  created   22-09-2017 08:00:01
 3      15     null         null     finished  22-09-2017 08:00:02
 4      16     <message>    null     pending   22-09-2017 08:00:05
 5      17     <message>    null     pending   22-09-2017 08:00:06
 6      17     null         null     pending   22-09-2017 08:00:06

我需要进行一个查询，该查询可以提取所有挂起的状态，但只需要最后一个。例如，我不想要id 3，因为它已经完成了状态。我想要4和6，因为这是最新的一套。但是，对于第5条，我确实想要消息，但在最后一个事件中没有保存它，因为存在超时或其他什么，因此所需的输出是：

id      job_id    request    response   status    date_time  
-----------------------------------------------------------
 4        16     <message>    null     pending   22-09-2017 08:00:05
 6        17     <message>    null     pending   22-09-2017 08:00:06

现在，我想出的查询还没有真正按照我想要的方式工作。

SELECT id, MAX(job_id), job_id
FROM testtable
HAVING status='pending'
GROUP BY id DESC;

我就是想不出如何把这两条信息混在一起。任何帮助都是非常感谢的。谢谢!

Answer 1

尝试获取最后一种状态挂起的寄存器。

DECLARE @table TABLE 
  ( 
     id         INT 
     ,job_id    INT 
     ,request   VARCHAR(10) 
     ,response  VARCHAR(10) 
     ,status    VARCHAR(10) 
     ,date_time DATETIME 
  ) 

INSERT @table (id,job_id,request,response,status,date_time ) Values
  (1, 15, '<message>',  null     ,'pending ','2017-09-22 08:00:00')
 ,(2, 15, null       ,'<message>','created ','2017-09-22 08:00:01')
 ,(3, 15, null       ,  null     ,'finished','2017-09-22 08:00:02')
 ,(4, 16, '<message>',  null     ,'pending ','2017-09-22 08:00:05')
 ,(5, 17, '<message>',  null     ,'pending ','2017-09-22 08:00:06')
 ,(6, 17, null       ,  null     ,'pending ','2017-09-22 08:00:06');


WITH cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    partition BY job_id 
                    ORDER BY id DESC) RowNumber 
                ,* 
         FROM   @table) 
SELECT * 
FROM   cte 
WHERE  rownumber = 1 
       AND status = 'pending' 

结果

RowNumber            id          job_id      request    response   status     date_time
-------------------- ----------- ----------- ---------- ---------- ---------- -----------------------
1                    4           16          <message>  NULL       pending    2017-09-22 08:00:05.000
1                    6           17          NULL       NULL       pending    2017-09-22 08:00:06.000

阅读您的评论，您需要这段代码。如果您没有不同的消息来处理相同的JobId，它将工作得很好。

 ;WITH cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    partition BY job_id 
                    ORDER BY id DESC) RowNumber 
                ,* 
         FROM   @table) 
SELECT Max(A.id)        Id 
       ,A.job_id 
       ,Max(IsNull(A.request ,''))  request 
       ,Max(IsNull(A.response,'')) response 
       ,Max(A.status)   status 
       ,Max(A.date_time)date_time 
FROM   @table A 
WHERE  EXISTS (SELECT * 
               FROM   cte B 
               WHERE  B.rownumber = 1 
                      AND B.status = 'pending' 
                      AND A.job_id = b.job_id) 
GROUP  BY A.job_id; 

结果

Id          job_id      request    response   status     date_time
----------- ----------- ---------- ---------- ---------- -----------------------
4           16          <message>             pending    2017-09-22 08:00:05.000
6           17          <message>             pending    2017-09-22 08:00:06.000

获取响应和请求的最后状态

;WITH cte 
     AS (SELECT Row_number() 
                  OVER ( 
                    partition BY job_id 
                    ORDER BY id DESC) RowNumber 
                ,* 
         FROM   @table), 
     cte_lstrequest 
     AS (SELECT Row_number() 
                  OVER ( 
                    partition BY job_id 
                    ORDER BY id DESC) RowNumber 
                ,* 
         FROM   @table 
         WHERE  status = 'pending' 
                AND request IS NOT NULL), 
     cte_lstrespponse 
     AS (SELECT Row_number() 
                  OVER ( 
                    partition BY job_id 
                    ORDER BY id DESC) RowNumber 
                ,* 
         FROM   @table 
         WHERE  status = 'pending' 
                AND response IS NOT NULL) 
SELECT A.id 
       ,A.job_id 
       ,B.request 
       ,C.response 
       ,A.status 
       ,Isnull(b.date_time, a.date_time) Id 
FROM   cte A 
       LEFT JOIN cte_lstrequest B 
              ON a.job_id = b.job_id 
                 AND a.rownumber = b.rownumber 
       LEFT JOIN cte_lstrespponse C 
              ON a.job_id = c.job_id 
                 AND a.rownumber = c.rownumber 
WHERE  a.rownumber = 1 
       AND a.status = 'pending'

结果

Id          job_id      request    response   status     Id
----------- ----------- ---------- ---------- ---------- -----------------------
4           16          <message>  NULL       pending    2017-09-22 08:00:05.000
6           17          <message>  NULL       pending    2017-09-22 08:00:06.000

Answer 2

SELECT MAX(id), job_id FROM testtable 
    WHERE status='pending' GROUP BY job_id DESC;

这将选择具有“挂起”状态的某些行的所有作业，选择作业具有“挂起”状态的最新ID。

Answer 3

row_number分区可以很容易地通过JobId按日期和/或id顺序完成，然后取所有row_number=1所在的行。

https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql