表中有一列为时间列
2011-08-03 19:25:27
2011-08-03 19:25:32
2011-08-03 19:25:37
2011-08-03 19:25:43
2011-08-03 19:25:48
2011-08-03 19:25:54
2011-08-03 19:25:59
2011-08-03 19:26:04
2011-08-03 19:26:10
2011-08-03 19:26:15
2011-08-03 19:26:20
2011-08-03 19:26:26
2011-08-03 19:26:31
2011-08-03 19:26:37
2011-08-03 19:26:43
2011-08-03 19:26:48
2011-08-03 19:26:53
2011-08-03 19:26:59
2011-08-03 19:27:04
2011-08-03 19:27:09
2011-08-03 19:27:15现在想从这个表中取数据,不想显示这么多记录,只想显示每分钟的第一条记录就行。
比如上面的记录 只想显示为
2011-08-03 19:25:27
2011-08-03 19:26:04
2011-08-03 19:27:04
2011-08-03 19:25:27
2011-08-03 19:25:32
2011-08-03 19:25:37
2011-08-03 19:25:43
2011-08-03 19:25:48
2011-08-03 19:25:54
2011-08-03 19:25:59
2011-08-03 19:26:04
2011-08-03 19:26:10
2011-08-03 19:26:15
2011-08-03 19:26:20
2011-08-03 19:26:26
2011-08-03 19:26:31
2011-08-03 19:26:37
2011-08-03 19:26:43
2011-08-03 19:26:48
2011-08-03 19:26:53
2011-08-03 19:26:59
2011-08-03 19:27:04
2011-08-03 19:27:09
2011-08-03 19:27:15现在想从这个表中取数据,不想显示这么多记录,只想显示每分钟的第一条记录就行。
比如上面的记录 只想显示为
2011-08-03 19:25:27
2011-08-03 19:26:04
2011-08-03 19:27:04
解决方案 »
- 请问t-sql基础有些了,但是数据库设计很没有感觉,想找本经典的数据库设计教程或网站来规范偶的数据库设计规范。
- 在一个触发器中如何判断是更新了数据还是插入了新数据???????????????????????????????
- 在SQLSERVER中,如何将一个数据库的一张表导入另外一个数据库
- 一个多表查询的问题,在线等ing...
- sql语句效率太低且报错
- 问一个极其弱智的问题,别笑我!(在线)
- 求一sql从新自动编号语句用set来执行的
- 一个select 语句的疑惑
- 在SQL SERVER 里怎样替换某一列的文件名
- 请问WEB助手向导中说可以设置WEB页的模版,请问它的格式是什么?
- 帮我写一个SQL语句吧
- 在线等!!!!急用
*
from
tb t
where
col=(select max(col1) from tb where convert(varchar(16),col,120)=convert(varchar(16),t.col,120))
*
from
tb t
where
col=(select min(col1) from tb where convert(varchar(16),col,120)=convert(varchar(16),t.col,120))
where not exists (select 1 from tb b
where datediff(minute, a.dt, b.dt) = 0
and b.dt < a.dt)
select row_number() over(partition by convert(varchar(100), datetimeObj, 23)+' '+convert(varchar(16),datepart(hh,datetimeObj))+':'+convert(varchar(16),datepart(mi,datetimeObj)) order by datetimeObj asc) as rowindex,* from table1) a1 where rowindex=1注释:datetimeObj 表内时间字段名 table1 表名
(
select '2011-08-03 19:25:27' as v_time union all
select '2011-08-03 19:25:32' union all
select '2011-08-03 19:25:37' union all
select '2011-08-03 19:25:43' union all
select '2011-08-03 19:25:48' union all
select '2011-08-03 19:25:54' union all
select '2011-08-03 19:25:59' union all
select '2011-08-03 19:26:04' union all
select '2011-08-03 19:26:10' union all
select '2011-08-03 19:26:15' union all
select '2011-08-03 19:26:20' union all
select '2011-08-03 19:26:26' union all
select '2011-08-03 19:26:31' union all
select '2011-08-03 19:26:37' union all
select '2011-08-03 19:26:43' union all
select '2011-08-03 19:26:48' union all
select '2011-08-03 19:26:53' union all
select '2011-08-03 19:26:59' union all
select '2011-08-03 19:27:04' union all
select '2011-08-03 19:27:09' union all
select '2011-08-03 19:27:15'
) ,
cte2 as
(
select
v_time,
ROW_NUMBER() over(partition by convert(varchar(16),v_time,120) order by v_time desc) as v_rk
from cte
)
select * from cte2
where v_rk='1'