SQL 分组获取产品 前两条记录

select * from
(
select *, ROW_NUMBER() over(partition by IPAddress order by recordtime desc) as rowNum
from MonitoringSystem
) A
where A.rowNum <= 1
order by A.IPAddress, A.recordtime desc

如果表中的数据是以秒记录的  但是显示的时候想以分钟显示 怎么办

 

 select * from MonitoringSystem
     where RecordTime in
       (
        select  MAX(RecordTime) from MonitoringSystem
        where IPAddress=@IPAddress
        group by convert(varchar(16),RecordTime,120) 
        )
    and RecordTime<=@CurrentTime 
    and RecordTime>=@PreTime
    and IPAddress=@IPAddress
    order by RecordTime asc

原表结构

USE [SimCloudV3.1]
GO

/****** Object:  Table [dbo].[MonitoringSystem]    Script Date: 12/06/2013 17:36:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MonitoringSystem](
    [ID] [nvarchar](50) NULL,
    [CPU] [nvarchar](50) NULL,
    [AvailableMemory] [nvarchar](50) NULL,
    [TotalMemory] [nvarchar](50) NULL,
    [RecordTime] [datetime] NULL,
    [AddTime] [datetime] NULL,
    [ChangeTime] [datetime] NULL,
    [DNSName] [nvarchar](50) NULL,
    [IPAddress] [nvarchar](50) NULL,
    [NetWork] [nvarchar](50) NULL,
    [SummaryState] [nvarchar](50) NULL
) ON [PRIMARY]

GO

 

 

 

转载于:https://www.cnblogs.com/ahghy/p/3461878.html