表A
字段:id (int主键自增) , value(float), timestamp(datetime)
timestamp可能有重复
我想去前10行timestamp 无重复的所有记录,请问该如何查?
我的SQL:select top 10 * from A order by timestamp desc,id
不过查询出来依然是timestamp有重复值,请高手赐教!
字段:id (int主键自增) , value(float), timestamp(datetime)
timestamp可能有重复
我想去前10行timestamp 无重复的所有记录,请问该如何查?
我的SQL:select top 10 * from A order by timestamp desc,id
不过查询出来依然是timestamp有重复值,请高手赐教!
FROM (
SELECT ROW_NUMBER() OVER(PARTITON BY timestamp ORDER BY id) as rid,
*
FROM tb
)tmp
WHERE rid=1
select * from A order by timestamp desc,id
where not exists(select 1 from A where t.timestamp=timestamp and t.id=id)
order by timestamp desc,id
order by timestamp desc
select top 10 id , value , timestamp from
(
select t.* , px = (select count(1) from a where timestamp = t.timestamp) + 1 from a t
) m
where px = 1
order by timestamp desc
select top 10 * from A t
where id = (select top 1 id from A where t.timestamp=timestamp and t.id=id)
order by timestamp desc,id
@@DBTS (Transact-SQL) 发送反馈
请参阅
全部折叠全部展开 语言筛选器: 全部语言筛选器: 多个语言筛选器: Visual Basic语言筛选器: C#语言筛选器: C++语言筛选器: J#语言筛选器: JScript
Visual Basic(Declaration)
C#
C++
J#
JScript返回当前数据库的当前 timestamp 数据类型的值。这一时间戳值在数据库中必须是唯一的。 Transact-SQL 语法约定语法
@@DBTS
返回类型
varbinary 备注
@@DBTS 返回当前数据库最后使用的时间戳值。插入或更新包含 timestamp 列的行时,将产生一个新的时间戳值。
--有重复的 数据 不要
select top 10 * from A t
where not exists(select 1 from A where t.timestamp=timestamp and t.id=id)
order by timestamp desc,id --有重复的 数据 要第一条
select top 10 * from A t
where id = (select top 1 id from A where t.timestamp=timestamp and t.id=id)
order by timestamp desc,id
select top 10 id , value , timestamp from
(
select t.* , px = (select count(1) from a where timestamp = t.timestamp and id < t.id) + 1 from a t
) m
where px = 1
order by timestamp descselect top 10 id , value , timestamp from
(
select t.* , px = (select count(1) from a where timestamp = t.timestamp and id > t.id) + 1 from a t
) m
where px = 1
order by timestamp desc
--sql 2005
select top 10 id , value , timestamp from
(
select t.* , px = row_number() over(partition by timestamp order by id) from a t
) m
where px = 1
order by timestamp descselect top 10 id , value , timestamp from
(
select t.* , px = row_number() over(partition by timestamp order by id desc) from a t
) m
where px = 1
order by timestamp desc
全错了,id忘改了
--有重复的 数据 不要
select top 10 * from A t
where not exists(select 1 from A where t.timestamp=timestamp and t.id<>id)
order by timestamp desc,id --有重复的 数据 要第一条
select top 10 * from A t
where id = (select top 1 id from A where t.timestamp=timestamp )
order by timestamp desc,id
他是datetime表A
字段:id (int主键自增) , value(float), timestamp(datetime)
select top 10 * from A t
where id = (select top 1 id from A where t.[timestamp]=[timestamp] order by id)
order by [timestamp] desc,id 补充2000的select top 10 * from A t
where id = (select min(id) from A where t.[timestamp]=[timestamp])
order by [timestamp] desc,id