有张表叫net_cap_port
结构为
CREATE TABLE `net_cap_port` (
`RecordTime` datetime NOT NULL COMMENT '采集时间',
`RoutId` int(11) NOT NULL COMMENT '路由Id',
`Portid` bigint(20) DEFAULT NULL COMMENT '端口Id',
`PortName` varchar(100) DEFAULT NULL COMMENT '端口名称',
`PortStatus` int(11) DEFAULT NULL COMMENT '端口状态',
`InputFlux` bigint(20) DEFAULT NULL COMMENT '端口入流量均值(入流量/带宽)',
`OutputFlux` bigint(20) DEFAULT NULL COMMENT '端口出流量均值(出流量/带宽)'
)数据为
RecordTime RoutId Portid PortName PortStatus InputFlux OutputFlux
'2011-06-29 12:15:59', '76', null, 'GigabitEthernet0/20', '1', '11', '1744'
'2011-06-30 12:15:59', '76', null, 'GigabitEthernet0/5', '1', '21', '1759'
'2011-07-01 12:15:59', '76', null, 'GigabitEthernet1/0/17', '1', , '10', '1652'
'2011-07-02 12:15:59', '76', null, 'GigabitEthernet1/0/16', '1', , '0', '1584'
'2011-06-29 12:15:59', '77', null, 'GigabitEthernet0/20', '1', '11', '1744'
'2011-06-30 12:15:59', '77', null, 'GigabitEthernet0/5', '1', '21', '1759'
'2011-07-01 12:15:59', '77', null, 'GigabitEthernet1/0/17', '1', , '10', '1652'
'2011-07-02 12:15:59', '77', null, 'GigabitEthernet1/0/16', '1', , '0', '1584'
'2011-06-29 12:15:59', '78', null, 'GigabitEthernet0/20', '1', '11', '1744'
'2011-06-30 12:15:59', '78', null, 'GigabitEthernet0/5', '1', '21', '1759'
'2011-07-01 12:15:59', '78', null, 'GigabitEthernet1/0/17', '1', , '10', '1652'
'2011-07-02 12:15:59', '78', null, 'GigabitEthernet1/0/16', '1', , '0', '1584'
。
这样格式的数据有168万条,我现在想以routid为单位,查询出每个portname的最后一次RecordTime记录,谢谢。我能写出SQL但效率不搞,望高手帮助。
结构为
CREATE TABLE `net_cap_port` (
`RecordTime` datetime NOT NULL COMMENT '采集时间',
`RoutId` int(11) NOT NULL COMMENT '路由Id',
`Portid` bigint(20) DEFAULT NULL COMMENT '端口Id',
`PortName` varchar(100) DEFAULT NULL COMMENT '端口名称',
`PortStatus` int(11) DEFAULT NULL COMMENT '端口状态',
`InputFlux` bigint(20) DEFAULT NULL COMMENT '端口入流量均值(入流量/带宽)',
`OutputFlux` bigint(20) DEFAULT NULL COMMENT '端口出流量均值(出流量/带宽)'
)数据为
RecordTime RoutId Portid PortName PortStatus InputFlux OutputFlux
'2011-06-29 12:15:59', '76', null, 'GigabitEthernet0/20', '1', '11', '1744'
'2011-06-30 12:15:59', '76', null, 'GigabitEthernet0/5', '1', '21', '1759'
'2011-07-01 12:15:59', '76', null, 'GigabitEthernet1/0/17', '1', , '10', '1652'
'2011-07-02 12:15:59', '76', null, 'GigabitEthernet1/0/16', '1', , '0', '1584'
'2011-06-29 12:15:59', '77', null, 'GigabitEthernet0/20', '1', '11', '1744'
'2011-06-30 12:15:59', '77', null, 'GigabitEthernet0/5', '1', '21', '1759'
'2011-07-01 12:15:59', '77', null, 'GigabitEthernet1/0/17', '1', , '10', '1652'
'2011-07-02 12:15:59', '77', null, 'GigabitEthernet1/0/16', '1', , '0', '1584'
'2011-06-29 12:15:59', '78', null, 'GigabitEthernet0/20', '1', '11', '1744'
'2011-06-30 12:15:59', '78', null, 'GigabitEthernet0/5', '1', '21', '1759'
'2011-07-01 12:15:59', '78', null, 'GigabitEthernet1/0/17', '1', , '10', '1652'
'2011-07-02 12:15:59', '78', null, 'GigabitEthernet1/0/16', '1', , '0', '1584'
。
这样格式的数据有168万条,我现在想以routid为单位,查询出每个portname的最后一次RecordTime记录,谢谢。我能写出SQL但效率不搞,望高手帮助。
select * from net_cap_port a where not exists(
select 1 from net_cap_port where a.routid=routid and a.portname=portname and a.RecordTime<RecordTime
)