如何将每10分钟之内的数据只显示一条(sql 2000)?
一个表中有多条时间记录,要求按时间排序,相邻2条记录需要相差10分钟以上create table a(dt datetime)insert into a(dt)
select '2012-12-21 14:19'
union
select '2012-12-21 14:24'
union
select '2012-12-21 14:28'
union
select '2012-12-21 14:39'
union
select '2012-12-21 14:44'
union
select '2012-12-21 14:52'
需要的结果:
'2012-12-21 14:19'
'2012-12-21 14:39'
'2012-12-21 14:52'或者查询出表中所有数据,将记录标识为是否有效,
(sql 2000版本 )2012-12-21 14:19:00.000 有效
2012-12-21 14:24:00.000 无效
2012-12-21 14:28:00.000 无效
2012-12-21 14:39:00.000 有效
2012-12-21 14:44:00.000 无效
2012-12-21 14:52:00.000 有效
一个表中有多条时间记录,要求按时间排序,相邻2条记录需要相差10分钟以上create table a(dt datetime)insert into a(dt)
select '2012-12-21 14:19'
union
select '2012-12-21 14:24'
union
select '2012-12-21 14:28'
union
select '2012-12-21 14:39'
union
select '2012-12-21 14:44'
union
select '2012-12-21 14:52'
需要的结果:
'2012-12-21 14:19'
'2012-12-21 14:39'
'2012-12-21 14:52'或者查询出表中所有数据,将记录标识为是否有效,
(sql 2000版本 )2012-12-21 14:19:00.000 有效
2012-12-21 14:24:00.000 无效
2012-12-21 14:28:00.000 无效
2012-12-21 14:39:00.000 有效
2012-12-21 14:44:00.000 无效
2012-12-21 14:52:00.000 有效
create table a(dt datetime)
insert into a(dt)
select '2012-12-21 14:19'
union
select '2012-12-21 14:24'
union
select '2012-12-21 14:28'
union
select '2012-12-21 14:39'
union
select '2012-12-21 14:44'
union
select '2012-12-21 14:52'DECLARE @minDt DATETIME
SET @minDt=(SELECT MIN(dt) FROM a)SELECT dt FROM a a WHERE NOT EXISTS(
SELECT 1 FROM a t WHERE DATEDIFF(mi,@minDt,a.dt)%10>DATEDIFF(mi,@minDt,t.dt)%10
AND DATEDIFF(mi,@minDt,a.dt)/10=DATEDIFF(mi,@minDt,t.dt)/10
)/*
dt
-----------------------
2012-12-21 14:19:00.000
2012-12-21 14:39:00.000
2012-12-21 14:52:00.000(3 行受影响)
*/DROP TABLE a
SET @minDt=(SELECT MIN(dt) FROM a)SELECT MIN(dt) AS dt FROM a
GROUP BY DATEDIFF(mi,@minDt,dt)/10
(
select dt from (
select DATEDIFF(mi,'1990-01-01 00:00:00',dt)/10 as dt from a)a
group by dt having count(1)=1
)b on DATEDIFF(mi,'1990-01-01 00:00:00',a.dt)/10=b.dt
(
select dt from (
select DATEDIFF(mi,'1990-01-01 00:00:00',dt)/10 as dt from a)a
group by dt having count(1)=1
)b on DATEDIFF(mi,'1990-01-01 00:00:00',a.dt)/10=b.dt结果:2012-12-21 14:19:00.000
有效 2012-12-21 14:24:00.000
无效 2012-12-21 14:28:00.000
无效 2012-12-21 14:39:00.000
有效 2012-12-21 14:44:00.000
有效 2012-12-21 14:52:00.000
有效
SELECT MIN(dt)
FROM (
SELECT dt , DATEDIFF (mi , (SELECT MIN (dt) FROM a) , a.dt)/ 10 AS d FROM a
) t
GROUP BY d
--2SELECT a.dt ,CASE WHEN t.dt IS NULL THEN '无效' ELSE '有效' END
from
(
SELECT MIN(dt) AS dt
FROM (
SELECT dt , DATEDIFF (mi , (SELECT MIN (dt) FROM a) , a.dt)/ 10 AS d FROM a
) t
GROUP BY d
) t RIGHT JOIN a ON t.dt=a.dt