如何用一条语句返回每台机器的最后一条记录

[skyzj]

系统不断的在读取每台正常运行的设备的某些参数
放到如下的表中：

机器编号，参数1，参数2，时间
1 0.15 0.22 ****
2 0.14 0.23 ****
2 0.15 0.32 ****
1 0.16 0.32 ****
1 0.15 0.22 ****
4 0.13 0.232 ****
1 0.15 0.22 ****

如何用一条语句得到截止到某个时间的，每台机器的最后一条记录？

谢谢！

[ws_hgo]

引用 5 楼 skyzj 的回复:

to josy: 检查了，的确最大的时间某些情况下有重复，也就是机器编号一样，时间一样，但是有一个自增量ID不一样

with r as
(
  select 机器编号，参数1，参数2，时间,row_number() over(partition by 机器编号,时间 order by 自增量ID) as [rank]
)
select 机器编号，参数1，参数2，时间 where [rank] = 1

[Ny-6000]

引用 2 楼 hyrongg 的回复:

2005 以上版本用row_number()over(partition by 机器编号 order by 时间 desc)

同意.

[---涛声依旧---]

----如果同一时同一台机器没有重复记录的话，可以用下面的查询 --测试 declare @t table(机器编号 int,参数1 numeric(10,2),参数2 numeric(10,2),时间 datetime) insert into @t select 1, 0.15 ,0.22, '2013-04-12 08:20:00' union all select 2 , 0.14 ,0.23, '2013-04-12 08:31:00' union all select 2 , 0.15 , 0.32 , '2013-04-12 08:29:00' union all select 1 , 0.16 , 0.32 , '2013-04-12 08:22:00' union all select 1 , 0.15 , 0.22 ,'2013-04-12 08:23:00' union all select 4 , 0.13 , 0.232 ,'2013-04-12 08:30:00' union all select 1 , 0.15, 0.22 ,'2013-04-12 08:27:22' --查询 select x.* from @t x inner join ( select 机器编号,MAX(时间) as 时间 from @t where convert(varchar(10),时间,120)<='2013-04-12 08:30:00' --截止到某个时间,楼主自己设置 group by 机器编号 ) y on x.机器编号=y.机器编号 and x.时间=y.时间 --结果 /* 机器编号 参数1 参数2 时间 4 0.13 0.23 2013-04-12 08:30:00.000 2 0.14 0.23 2013-04-12 08:31:00.000 1 0.15 0.22 2013-04-12 08:27:22.000 */

[---涛声依旧---]

--如果同一时间同一台机器有重复记录的话，则用下面查询 --搭建测试环境 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb]') AND type in (N'U')) DROP TABLE [dbo].tb GO create table tb (自增量ID int IDENTITY(1,1) NOT NULL,机器编号 int,参数1 numeric(10,2),参数2 numeric(10,2),时间 datetime) insert into tb select 1, 0.15 ,0.22, '2013-04-12 08:20:00' union all select 2 , 0.14 ,0.23, '2013-04-12 08:31:00' union all select 2 , 0.15 , 0.32 , '2013-04-12 08:29:00' union all select 1 , 0.16 , 0.32 , '2013-04-12 08:20:00' union all select 1 , 0.15 , 0.22 ,'2013-04-12 08:23:00' union all select 1 , 0.16 , 0.32 , '2013-04-12 08:22:00' union all select 1 , 0.88 , 0.99 ,'2013-04-12 08:23:00' union all select 4 , 0.13 , 0.23 ,'2013-04-12 08:30:00' union all select 1 , 0.15, 0.22 ,'2013-04-12 08:22:00' --查询 with t as ( select 机器编号,参数1,参数2,时间,row_number() over(partition by 机器编号,时间 order by 自增量ID desc --ws_hgo 少了desc，楼主要求取最后一笔记录 ) as [rank] from tb ) select 机器编号,参数1,参数2,时间 from t where [rank] = 1 --测试结果 /* 机器编号 参数1 参数2 时间 1 0.16 0.32 2013-04-12 08:20:00.000 1 0.15 0.22 2013-04-12 08:22:00.000 1 0.88 0.99 2013-04-12 08:23:00.000 2 0.15 0.32 2013-04-12 08:29:00.000 2 0.14 0.23 2013-04-12 08:31:00.000 4 0.13 0.23 2013-04-12 08:30:00.000 */

[百年树人]

select * 
  from tb t
    where not exists(select 1 from tb where 机器编号=t.机器编号 and 时间>t.时间)

[hyrongg]

2005 以上版本用row_number()over(partition by 机器编号 order by 时间 desc)

[skyzj]

to josy: 不对啊：还是会返回每台机器的很多条记录，我只要最后一条，某个时间点前的

[百年树人]

引用 3 楼 skyzj 的回复:

to josy: 不对啊：还是会返回每台机器的很多条记录，我只要最后一条，某个时间点前的

返回同一台机器的记录时间是不是都是相同的？如果最大的时间有重复，你得从中选取一个出来，以什么原则来取？

[skyzj]

to josy: 检查了，的确最大的时间某些情况下有重复，也就是机器编号一样，时间一样，但是有一个自增量ID不一样

[连星入剑端]

话说，如果你还有个机器信息表的话，或许可以用下APPLY，sql server 2005及以上版本适用。

-- 机器概况表；
DECLARE @c TABLE ( 机器编号 INT );

INSERT  INTO @c
VALUES  ( 1 ),
        ( 2 ),
        ( 3 ),
        ( 4 );
        
-- 机器记录表；
DECLARE @t TABLE
    (
      机器编号 INT ,
      参数1 NUMERIC(10, 2) ,
      参数2 NUMERIC(10, 2) ,
      时间 DATETIME
    )
INSERT  INTO @t
VALUES  ( 1, 0.15, 0.22, '2013-04-12 08:20:00' ),
        ( 2, 0.14, 0.23, '2013-04-12 08:31:00' ),
        ( 2, 0.15, 0.32, '2013-04-12 08:29:00' ),
        ( 1, 0.16, 0.32, '2013-04-12 08:22:00' ),
        ( 1, 0.15, 0.22, '2013-04-12 08:23:00' ),
        ( 4, 0.13, 0.232, '2013-04-12 08:30:00' ),
        ( 1, 0.15, 0.22, '2013-04-12 08:27:22' );

-- 查询语句；
SELECT  tt.*
FROM    @c c
        CROSS APPLY ( SELECT TOP 1
                                *
                      FROM      @t t
                      WHERE     c.机器编号 = t.机器编号
                      ORDER BY  时间 desc
                    ) AS tt;