你试试;WITH cte AS (SELECT MIN(happentime) mhappentime , MAX(happentime) mahappentime FROM 表 WHERE varname = '变量名' AND 其他where 条件)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 )
;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 )
在上面的代码基础上,关联了一下你的表t_data_201309: ;WITH cte AS (SELECT MIN(happentime) mhappentime , MAX(happentime) mahappentime FROM t_data_201309 WHERE varname = 'WTH10_2_Temp')SELECT *,t.varname ,t.datavalue FROM ( SELECT DATEADD(minute, number * 30, (SELECT mhappentime FROM cte)) happentime FROM master..spt_values a WHERE type = 'p' AND number > 0 ) a inner join t_data_201309 t on a.happentime = t.happentime 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' ) a WHERE EXISTS ( SELECT 1 FROM cte b WHERE a.happentime BETWEEN b.mhappentime AND b.mahappentime )
select convert(varchar(16),happentime,120) as min_slice,varname,vardesc,datavalue from t_data_201309 where convert(varchar(16),happentime,120) in (SELECT convert(varchar(16),min(happentime),120) FROM (SELECT N=DATEDIFF(N,(SELECT MIN(happentime) FROM t_data_201309),happentime)/30, happentime FROM t_data_201309) AS T GROUP BY N ) and varname in ('WTH10_2_Humidity','WTH10_2_status','WTH10_2_Temp') and happentime between '2013-09-16 00:00:00' and '2013-09-26 11:15:00'这是现在MSSQL用的语句,能查到数据,不过多表联查有点不太好改 我再看看,多谢两位
MAX(happentime) mahappentime
FROM 表
WHERE varname = '变量名' AND 其他where 条件)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 )
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 )
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 )
不过只有happentime
请问其它字段,像varname,value这些怎么一起出来?本人太菜了(-_")
2013-09-13 11:20:46.000 WTH10_2_Temp 30.3
2013-09-13 11:30:47.000 WTH10_2_Temp 30.2
2013-09-13 11:44:45.000 WTH10_2_Temp 30.3
2013-09-13 11:54:45.000 WTH10_2_Temp 30.3
2013-09-13 12:04:45.000 WTH10_2_Temp 30.4
2013-09-13 12:14:46.000 WTH10_2_Temp 30.3
2013-09-13 12:24:46.000 WTH10_2_Temp 32.1
2013-09-13 12:34:46.000 WTH10_2_Temp 30.2
2013-09-13 12:44:47.000 WTH10_2_Temp 30.2
2013-09-13 12:54:47.000 WTH10_2_Temp 30
2013-09-13 13:04:47.000 WTH10_2_Temp 29.9
2013-09-13 13:14:48.000 WTH10_2_Temp 30
2013-09-13 13:24:48.000 WTH10_2_Temp 29.9
2013-09-13 13:34:48.000 WTH10_2_Temp 30
2013-09-13 13:44:49.000 WTH10_2_Temp 29.9
2013-09-13 13:54:49.000 WTH10_2_Temp 29.9
2013-09-13 14:04:49.000 WTH10_2_Temp 30
2013-09-13 14:14:50.000 WTH10_2_Temp 29.9
2013-09-13 14:24:50.000 WTH10_2_Temp 29.9
2013-09-13 14:34:50.000 WTH10_2_Temp 29.9
2013-09-13 14:44:50.000 WTH10_2_Temp 31.5
2013-09-13 14:57:52.000 WTH10_2_Temp 31.4
2013-09-13 15:25:56.000 WTH10_2_Temp 30
2013-09-13 18:02:37.000 WTH10_2_Temp 30.1
2013-09-13 18:17:10.000 WTH10_2_Temp 30.1
2013-09-13 18:27:10.000 WTH10_2_Temp 30.1
2013-09-13 18:37:11.000 WTH10_2_Temp 30.2
2013-09-13 18:47:11.000 WTH10_2_Temp 30.2
2013-09-13 18:57:11.000 WTH10_2_Temp 30.2
2013-09-14 09:46:40.000 WTH10_2_Temp 30.4
2013-09-14 09:56:40.000 WTH10_2_Temp 30.4
2013-09-14 10:06:40.000 WTH10_2_Temp 30.4
2013-09-14 10:16:41.000 WTH10_2_Temp 30.4
2013-09-14 10:26:41.000 WTH10_2_Temp 30.4
2013-09-14 10:36:41.000 WTH10_2_Temp 30.4
2013-09-14 10:46:41.000 WTH10_2_Temp 30.4
2013-09-14 10:56:42.000 WTH10_2_Temp 30.5
2013-09-14 11:29:56.000 WTH10_2_Temp 30.5
2013-09-14 11:44:26.000 WTH10_2_Temp 30.5
2013-09-14 11:59:53.000 WTH10_2_Temp 30.4
2013-09-14 12:09:53.000 WTH10_2_Temp 30.4
2013-09-14 12:19:54.000 WTH10_2_Temp 30.5
2013-09-14 12:29:54.000 WTH10_2_Temp 30.5
2013-09-14 12:39:54.000 WTH10_2_Temp 30.5
2013-09-14 12:49:54.000 WTH10_2_Temp 30.5
2013-09-14 12:59:55.000 WTH10_2_Temp 30.5有劳
;WITH cte AS (SELECT MIN(happentime) mhappentime ,
MAX(happentime) mahappentime
FROM t_data_201309
WHERE varname = 'WTH10_2_Temp')SELECT *,t.varname ,t.datavalue
FROM ( SELECT DATEADD(minute, number * 30, (SELECT mhappentime FROM cte)) happentime
FROM master..spt_values a
WHERE type = 'p'
AND number > 0
) a
inner join t_data_201309 t
on a.happentime = t.happentime
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'
) a
WHERE EXISTS ( SELECT 1
FROM cte b
WHERE a.happentime BETWEEN b.mhappentime
AND b.mahappentime )
我再看看,多谢两位