代码之家 › 专栏 › 技术社区 › Matt Howells

确定状态的SQL查询?

sql-server sql

Matt Howells · 技术社区 · 16 年前

我在MSSQL数据库中有一个表,如下所示:

Timestamp (datetime)
Message (varchar(20))

一天一次,一个特定的进程在启动时插入当前时间和消息“started”。完成后,插入当前时间和消息“Finished”。

给定一个特定的日期,一个好的查询或一组语句返回:

如果进程从未启动,则为0
1如果进程已开始但未完成
2如果过程开始和结束

表中还有其他消息,但“已开始”和“已完成”对此进程是唯一的。

编辑:对于奖励业力,如果数据无效,则会引发错误,例如有两条“已启动”消息,或者有一条“已完成”但没有“已启动”。

4 回复 | 直到 16 年前

George Mastros 16 年前

Select Count(Message) As Status
From   Process_monitor
Where  TimeStamp >= '20080923'
       And TimeStamp < '20080924'
       And (Message = 'Started' or Message = 'Finished')

您可以稍微修改一下,以检测无效的条件,例如多次启动、完成、没有完成的启动等等。

Select  Case When SumStarted = 0 And SumFinished = 0 Then 'Not Started'
             When SumStarted = 1 And SumFinished = 0 Then 'Started'
             When SumStarted = 1 And SumFinished = 1 Then 'Finished'
             When SumStarted > 1 Then 'Multiple Starts' 
             When SumFinished > 1 Then 'Multiple Finish'
             When SumFinished > 0 And SumStarted = 0 Then 'Finish Without Start'
             End As StatusMessage
From    (
          Select Sum(Case When Message = 'Started' Then 1 Else 0 End) As SumStarted,
                 Sum(Case When Message = 'Finished' Then 1 Else 0 End) As SumFinished
          From   Process_monitor
          Where  TimeStamp >= '20080923'
                 And TimeStamp < '20080924'
                 And (Message = 'Started' or Message = 'Finished')
        ) As AliasName

Aheho 16 年前

缺少唯一标识进程的列。让我们添加一个名为processid的int列。您还需要另一个表来标识流程。如果您依赖于原始表,则永远不会知道从未启动的进程,因为该进程没有任何行。

select
    ProcessID,
    ProcessName,

    CASE
    WHEN 
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'STARTED') = 1 

        And
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'FINISHED') = 0
     THEN 1

     WHEN
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'STARTED') = 1 
       And
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'FINISHED') = 1 
THEN 2
     ELSE 0

END as Status

From
    Processes

Amy B 16 年前

DECLARE @TargetDate datetime
SET @TargetDate = '2008-01-01'

DECLARE @Messages varchar(max)

SET @Messages = ''

SELECT @Messages = @Messages + '|' + Message
FROM process_monitor
WHERE @TargetDate <= Timestamp and Timestamp < DateAdd(dd, 1, @TargetDate)
   and Message in ('Finished', 'Started')
ORDER BY Timestamp desc

SELECT CASE
  WHEN @Messages = '|Finished|Started' THEN 2
  WHEN @Messages = '|Started' THEN 1
  WHEN @Messages = '' THEN 0
  ELSE -1
END

-1

Grant Johnson 16 年前

select count(*) from process_monitor 
where timestamp > yesterday and timestamp < tomorrow.

或者,可以使用带有max的self-join来显示特定日期的最新消息:

select * from process_monitor where 
timestamp=(select max(timestamp) where timestamp<next_day);