22,207
社区成员
发帖
与我相关
我的任务
分享
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
time datetime,
data1 float,
data2 int,
data3 int
)
go
--插入测试数据
insert into tb select '2010-4-27 0:00:00',7.2,26,56
union all select '2010-4-27 1:00:00',7.1,26,58
union all select '2010-4-27 2:00:00',7.3,26,60
union all select '2010-4-27 3:00:00',8.0,27,62
union all select '2010-4-27 4:00:00',8.2,27,64
union all select '2010-4-27 5:00:00',7.3,26,64
go
--代码实现
select time,data1,data2,data3 from (
select idd=row_number()over( partition by data2 order by time),* from tb)m
where idd=1
/*测试结果
time data1 data2 data3
----------------------------------------------
2010-04-27 00:00:00.000 7.2 26 56
2010-04-27 03:00:00.000 8 27 62
(2 行受影响)
*/