Null、Zero和Constant数据的SQL查询

Question

我有一个查询，我只想修改它(这个查询不是我写的)。

SELECT  CAST(DATEPART(yyyy, UTCTimeStamp) AS VARCHAR(4)) + '-'
        + CAST(DATEPART(mm, UTCTimeStamp) AS VARCHAR(2)) + '-'
        + CAST(DATEPART(dd, UTCTimeStamp) AS VARCHAR(2)) + ' '
        + CAST(DATEPART(hh, UTCTimeStamp) AS VARCHAR(2)) + ':00:000 - '
        + CAST(DATEPART(yyyy, UTCTimeStamp) AS VARCHAR(4)) + '-'
        + CAST(DATEPART(mm, UTCTimeStamp) AS VARCHAR(2)) + '-'
        + CAST(DATEPART(dd, UTCTimeStamp) AS VARCHAR(2)) + ' '
        + CAST(DATEPART(hh, UTCTimeStamp) AS VARCHAR(2)) + ':45:000' AS HourTime ,
        SUM(CASE WHEN ElapsedValue IS NULL THEN 1
                 ELSE 0
            END) AS NoData ,
        SUM(CASE WHEN ElapsedValue = 0 THEN 1
                 ELSE 0
            END) AS ZeroData
FROM    tblLive_TrendLog_15Min
WHERE   ISNULL(ElapsedValue, 0) = 0
GROUP BY CAST(DATEPART(yyyy, UTCTimeStamp) AS VARCHAR(4)) + '-'
        + CAST(DATEPART(mm, UTCTimeStamp) AS VARCHAR(2)) + '-'
        + CAST(DATEPART(dd, UTCTimeStamp) AS VARCHAR(2)) + ' '
        + CAST(DATEPART(hh, UTCTimeStamp) AS VARCHAR(2)) + ':00:000 - '
        + CAST(DATEPART(yyyy, UTCTimeStamp) AS VARCHAR(4)) + '-'
        + CAST(DATEPART(mm, UTCTimeStamp) AS VARCHAR(2)) + '-'
        + CAST(DATEPART(dd, UTCTimeStamp) AS VARCHAR(2)) + ' '
        + CAST(DATEPART(hh, UTCTimeStamp) AS VARCHAR(2)) + ':45:000'

当我运行此查询时，它将显示：

HourlyTime                          ZeroData       NULL    
2013-6-8 23:00:000 - 2013-6-8 23:45:000 0   40    
2013-6-8 3:00:000 - 2013-6-8 3:45:000   0   52    
2013-6-8 7:00:000 - 2013-6-8 7:45:000   0   52    
2013-6-9 3:00:000 - 2013-6-9 3:45:000   0   52    
2013-6-9 4:00:000 - 2013-6-9 4:45:000   0   52    
2010-1-1 12:00:000 - 2010-1-1 12:45:000 0   36    
2010-1-1 13:00:000 - 2010-1-1 13:45:000 0   27    
2010-1-1 15:00:000 - 2010-1-1 15:45:000 0   30

基本上，它每小时计算一次零值和空值。如果您想了解更多信息，请访问链接：SQL Programming combining 4 rows into 1 and adding 3 columns

现在，我只想向该查询添加另一列，该列将计算是否有任何常量值，然后只显示常量值的数量(应该介于0和3之间)例如，如果2013-05-4 10:30处的值与2013-05-4 10:15处的值相同，则ConstantData列中的值应该为1。基本上，每15分钟将该值与前一个值进行比较。

Answer 1

我相信这是一个重复的问题，但我只想指出，使用SQL 2012，您可以使用FORMAT函数极大地简化查询：

SELECT  FORMAT(UTCTimeStamp ,'yyyy-MM-dd hh:00:000 - yyyy-MM-dd hh:45:000 ')AS HourTime ,
        SUM(CASE WHEN ElapsedValue IS NULL THEN 1
                 ELSE 0
            END) AS NoData ,
        SUM(CASE WHEN ElapsedValue = 0 THEN 1
                 ELSE 0
            END) AS ZeroData
FROM    tblLive_TrendLog_15Min
WHERE   ISNULL(ElapsedValue, 0) = 0
GROUP BY FORMAT(UTCTimeStamp ,'yyyy-MM-dd hh:00:000 - yyyy-MM-dd hh:45:000 ')