请大神帮忙变换一条SQL语句

2014-05-12

请大神帮忙转换一条SQL语句[PostgreSQL]WITH x AS (SELECT date_trunc(minute, happentime) AS min_slic

请大神帮忙转换一条SQL语句
[PostgreSQL]
WITH x AS (SELECT date_trunc('minute', happentime) AS min_slice,varname,value FROM 表 where varname='变量名')
SELECT DISTINCT m.min_slice, count(x.min_slice) OVER (ORDER BY m.min_slice) AS running_ct, varname, value FROM (SELECT generate_series(min(min_slice), max(min_slice), '30m') AS min_slice FROM x) m
LEFT JOIN x USING (min_slice) ORDER BY 1

请大神帮忙变换一条SQL语句

上面的语句是从国外论坛上找到的，可以从数据库中每隔30分钟取一个数据出来
时间间隔很工整，即使那个时间段没有数据也会插入一条空的方便后续处理
请高手帮忙写一个MS SQL类似的能用的语句
万分感谢！！！
[解决办法]

引用:

;WITH cte AS (SELECT MIN(happentime) mhappentime ,
                                    MAX(happentime) mahappentime
                          FROM      t_data_201309
                          WHERE     varname = 'WTH10_2_Temp')

SELECT  *
FROM    ( SELECT    DATEADD(minute, number * 30, (SELECT mhappentime FROM cte)) happentime
          FROM      master..spt_values a
          WHERE     type = 'p'
                    AND number > 0
        ) b
WHERE   EXISTS ( SELECT 1
                 FROM  cte b
                 WHERE  a.happentime BETWEEN b.mhappentime
                                     AND     b.mahappentime )

错误信息
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "a.happentime"。
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "a.happentime"。

感谢版主

改成这样试试呢：

;WITH cte AS (SELECT MIN(happentime) mhappentime ,
                                    MAX(happentime) mahappentime
                          FROM      t_data_201309
                          WHERE     varname = 'WTH10_2_Temp')

SELECT  *
FROM    ( SELECT    DATEADD(minute, number * 30, (SELECT mhappentime FROM cte)) happentime
          FROM      master..spt_values a
          WHERE     type = 'p'
                    AND number > 0
        ) a
WHERE   EXISTS ( SELECT 1
                 FROM  cte b
                 WHERE  a.happentime BETWEEN b.mhappentime
                                     AND     b.mahappentime )

[解决办法]
;WITH cte AS (SELECT MIN(happentime) mhappentime ,
                                    MAX(happentime) mahappentime
                          FROM      t_data_201309
                          WHERE     varname = 'WTH10_2_Temp')

SELECT  *
FROM    ( SELECT    DATEADD(minute, number * 30, (SELECT mhappentime FROM cte)) happentime
          FROM      master..spt_values a
          WHERE     type = 'p'
                    AND number > 0
        ) a
WHERE   EXISTS ( SELECT 1
                 FROM  cte b
                 WHERE  a.happentime BETWEEN b.mhappentime
                                     AND     b.mahappentime )

热点排行

SQL Server

请大神帮忙变换一条SQL语句