根据子项当前状态查询

Question

正在尝试编写查询以显示公司部门中服务的当前状态。我所写的东西就是不起作用。我觉得我走错了方向。

SELECT MAX(v_StatusEvents.EventTimeStamp) as EventTimeStamp
    , MAX(v_StatusEvents.StatusTypeID) as StatusTypeID
    , v_StatusEvents.ServiceID
    , v_StatusEvents.StatusTypeDescription
    , v_StatusEvents.ServiceName
    , v_StatusEvents.CategoryName
FROM v_StatusEvents
WHERE v_StatusEvents.CategoryID = 100
    AND YEAR(v_StatusEvents.EventTimeStamp) = YEAR(getdate())
    AND MONTH(v_StatusEvents.EventTimeStamp) = MONTH(getdate())
    AND DAY(v_StatusEvents.EventTimeStamp) = DAY(getdate())
GROUP BY  v_StatusEvents.ServiceID
    , v_StatusEvents.StatusTypeDescription
    , v_StatusEvents.ServiceName
    , v_StatusEvents.CategoryName

我有三个类别：(100 -互联网，101 -应用程序和102 -网络)。

每个类别都包含服务。

例如，我有三个属于Interenet类别的服务：(50 -内部，51 -外部，52 -开发)。

每个服务始终至少有一条当前日期的状态记录。

当前状态将设置为三种不同状态类型值之一:1=无问题，2=中断，3=严重。

我想要显示今天每个类别的最高状态类型。

以下是今天日期的示例记录集。

SeID   CatID    EventTimeStamp         SvcID  StatTypeID  
201     100     11/11/2012 12:01am     52     1     
202     100     11/11/2012 12:01am     51     1     
203     100     11/11/2012 12:01am     50     1     
204     100     11/11/2012 08:00am     51     3  
205     100     11/11/2012 10:50am     50     2     
206     100     11/11/2012 11:00am     50     1     
207     100     11/11/2012 11:25am     52     2 

如您所见，内部网站在上午10:50出现了中断问题，但在上午11:00解决了问题

外部网站存在一个持续的关键问题，尚未解决。我希望查询返回值3，因为这是尚未解析的服务的最高当前状态。

(如果所有服务都有“无问题”，我希望查询返回值为1)

谢谢,

垃圾

Answer 1

最终提出了以下解决方案：

SELECT (IsNull(MAX( tblStatusTypes.StatusTypeImgURL )，(SELECT tblStatusTypes.StatusTypeImgURL FROM tblStatusTypes WHERE tblStatusTypes.StatusTypeID =1) FROM tblStatusTypes WHERE tblStatusTypes.StatusTypeID in (

SELECT MAX(StatusTypeID)
FROM
(   SELECT vse.StatusTypeID, vse.ServiceID, vse.EventTimeStamp
    FROM v_StatusEvents vse,
        (SELECT MAX(EventTimeStamp)AS MaxDate
            ,ServiceID
        FROM   v_StatusEvents 
        WHERE  EventTimeStamp >= Cast(Cast(CURRENT_TIMESTAMP AS DATE) AS DATETIME) 
        AND CategoryID = @CategoryID
        GROUP  BY ServiceID) MaxResults
    WHERE vse.ServiceID = MaxResults.ServiceID
    AND vse.EventTimeStamp = MaxResults.MaxDate
) MaxStatusType )

Answer 2

这就是我发现CTE(常用表表达式)有用的地方。它们允许您将问题分解为您可以轻松解决的步骤。让我们在这里应用它。

首先，获取每个服务/天的最大状态：

 SELECT CatID, SvcID, MAX(StatTypeID) As MaxStatus
 FROM v_StatusEvents
 WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime)
 GROUP BY CatID, SvcID

现在我们有了这些信息，我们可以找到今天发生这些事件的最新时间：

WITH StatusInfo As 
(
    SELECT CatID, SvcID, MAX(StatTypeID) As MaxStatus
    FROM v_StatusEvents
    WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime)
    GROUP BY CatID, SvcID
)
SELECT se.CatID, se.SvcID, se.StatTypeID, MAX(EventTimeStamp) As EventTimeStamp
FROM v_StatusEvents se
INNER JOIN StatusInfo si ON se.CatID = si.CatID AND se.SvcID = si.SvcID AND se.StatTypeID = si.MaxStatus
WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime)

在这里，您可以选择使用序列ID来缩小范围，以防一个服务有两个具有相同时间戳的事件。现在我们有了这个信息，我们可以再一次回到表中，选取我们可能需要的任何其他字段(在本例中是序列ID)：

WITH StatusInfo As 
(
    SELECT CatID, SvcID, MAX(StatTypeID) As MaxStatus
    FROM v_StatusEvents
    WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime)
    GROUP BY CatID, SvcID
), StatusAndTimeInfo As
(
    SELECT se.CatID, se.SvcID, se.StatTypeID, MAX(EventTimeStamp) As EventTimeStamp
    FROM v_StatusEvents se
    INNER JOIN StatusInfo si ON se.CatID = si.CatID AND se.SvcID = si.SvcID AND se.StatTypeID = si.MaxStatus
    WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime)
)
SELECT se.*
FROM v_StatusEvents se
INNER JOIN StatusAndTimeInfo sati ON se.CatID = sati.CatID AND se.SvcID = sati.SvcID AND se.StatTypeID = sati.StatTypeID AND se.EventTimeStamp = sati.EventTimeStamp
WHERE EventTimeStamp >= cast(cast(current_timestamp as Date) as datetime)

再次注意，您可能更喜欢在最后一次迭代中使用SeID (我假设它是一个序列ID)，而不是使用时间戳。还请注意，这不是解决此问题的唯一方法，甚至可能是最快的方法。实际上，可以仅使用子查询或连接重写此代码。但这是任何简单的方法，您可以使用它来获得有效的东西，并且稍后可以很容易地理解。