問題描述

假設(shè)您的 SQL Server(目前是 SQL Server 2008)數(shù)據(jù)庫中有一個(gè)如下所示的表:

Say you had a table in your SQL Server ( prensently SQL Server 2008) database that look like this:

Date                    Id State
=================================
2013-09-12 15:02:41,844 1 OK
2013-09-12 15:02:41,844 2 OK
2013-09-12 15:02:41,844 3 ERROR
2013-09-12 15:02:41,844 4 ERROR
2013-09-12 15:02:41,844 5 ERROR
2013-09-13 15:02:41,844 1 ERROR
2013-09-14 15:02:41,844 1 OK
2013-09-15 15:02:41,844 1 ERROR
2013-09-15 15:02:41,844 2 ERROR

表的目的是保存記錄狀態(tài).我寫了這張表，但現(xiàn)在我不知道如何查詢它以獲得時(shí)間序列中不同狀態(tài)的概覽.

The purpouse of the table is to save records state. I wrote this table but now i cannot figure out how to query it for an overview of the different states during a time sequence.

我正在尋找的結(jié)果是:

2013-09-12 16:00:00 OK 2
2013-09-12 16:00:00 ERROR 3
2013-09-13 16:00:00 OK 1
2013-09-13 16:00:00 ERROR 4
2013-09-14 16:00:00 OK 2
2013-09-14 16:00:00 ERROR 3
2013-09-15 16:00:00 OK 0
2013-09-15 16:00:00 ERROR 5

編輯:我想要實(shí)現(xiàn)的是我的對象狀態(tài)的日常視圖.哪些對象有錯(cuò)誤，哪些沒問題.也許車庫是一個(gè)更好的例子，但我會堅(jiān)持這個(gè).

EDIT: What i am trying to achieve is a day-by-day view of the state of my objects. which objects has an error and which ones are ok. Maybe a garage would be a better example, but i will stick to this.

• 12 日，3 個(gè)對象出現(xiàn)錯(cuò)誤，2 個(gè)對象正常.
• 在 13 日，一個(gè)對象從 OK 變?yōu)?Error.我現(xiàn)在有 4 個(gè)對象處于 ERROR 狀態(tài)，一個(gè)正常.
• 在 14 日，我的 ID 為 1 的對象已修復(fù)，現(xiàn)在我又回到了兩個(gè)正常的對象和三個(gè)具有錯(cuò)誤狀態(tài)的對象

我已經(jīng)想出了(找到)如何像這樣生成日期序列(不使用變量聲明):

I have figured out (found) how to generate a sequence of dates (without using variable declarations) like this:

;WITH dates
AS (
    SELECT CAST('2013-12-17 16:00:00' AS DATETIME) 'date'

    UNION ALL

    SELECT DATEADD(dd, 1, t.DATE)
    FROM dates t
    WHERE DATEADD(dd, 1, t.DATE) <= GETDATE()
    )
SELECT dates.DATE   
FROM dates

而且我有一個(gè)查詢，它執(zhí)行相關(guān)分組(我認(rèn)為)以提供所需的輸出(和點(diǎn)):

And i have a query that does the relevant grouping (i think) that delivers a desired output (and point):

Date State Count(state)
=======================    
2013-09-12 16:00:00 OK 2
        2013-09-12 16:00:00 ERROR 3
        2013-09-13 16:00:00 ERROR 1
        2013-09-14 16:00:00 OK 1
        2013-09-15 16:00:00 ERROR 1

那么問題是，你如何將日期序列與我的分組結(jié)果結(jié)合起來以達(dá)到預(yù)期的結(jié)果.

So the question is, how do you marry the sequence of dates to my grouping result to achieve the desired result.

推薦答案

通過 SQL Server 2012 提供的窗口函數(shù)，您可以使用以下查詢:

With SQL Server 2012 providing window functions you can use the following query:

SELECT date,
       sum(ok_mod) over (order by date) as ok
FROM (
  SELECT date,
         sum(case when state = 'OK' then 1
                  when state = 'ERROR' and prev_state is not null then -1
                  else 0
             end) as ok_mod
  FROM (
    SELECT date, id, state,
           lag(state) over (partition by id order by date) prev_state
    FROM data
  ) AS data
  GROUP BY date
) AS data
ORDER BY date;

這僅給您 OK 部分，但您也可以輕松地將 ERROR 部分的相應(yīng)查詢聯(lián)合起來.SQL Fiddle 此處.

This gives you only OK part, but you can easily union corresponding query for ERROR part also. SQL Fiddle here.

使用 2008 版本恕我直言，如果沒有過程或非常復(fù)雜的查詢，您將無法實(shí)現(xiàn).

With 2008 version imho you won't make it without a procedure or really complex query.

這篇關(guān)于按日期或時(shí)間順序查看分組記錄.可能是按級別分組的日志表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！