select * from (select ae.cid as scn,timestamp, inet_ntoa(ip_src) as 'srcip', layer4_sport as 'srcport', inet_ntoa(ip_dst) as 'dstip',layer4_dport as 'dstport', data_payload as 'data'
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6) as temp where rowid in (select max(rowid)from
(select sid,timestamp,data, rowid from temp)group by timestamp,data); 上面是oracle的样式,怎么写放在mysql也能用呢?
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6) as temp where rowid in (select max(rowid)from
(select sid,timestamp,data, rowid from temp)group by timestamp,data); 上面是oracle的样式,怎么写放在mysql也能用呢?
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6) as temp where rowid in (select max(rowid)from
(select sid,timestamp,data, rowid from temp)group by timestamp,data);就是我想把查询后的temp表中timestamp 和data 重复的内容去掉
比如
sid timestamp data
1 2009-07-27 09:49:31 123456
2 2009-07-27 09:49:31 123456
这个只能算一条
比如
sid timestamp data
1 2009-07-27 09:49:31 123456
2 2009-07-27 09:49:31 123456
这个只能算一条下面语句保留重复项的SID最小的一条。
select *
from temp t
where not exist (select 1 from temp where `timestamp`=t.timestamp and data=t.data and sid<t.sid)
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6)temp as t
where not exist (select 1 from temp where `timestamp`=t.timestamp and data=t.data and sid<t.sid)这样写不行呀 为什么呢?
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6) as temp where rowid in (select max(rowid)from
(select sid,timestamp,data, rowid from temp)group by timestamp,data); ---------------
你的这个语句在oracle行能正常跑?
语法、别名引用都已经有问题了如果你不想用中间临时表的话,非要一句语句搞定的话,这样吧:select * from
(select ae.cid as scn,timestamp, inet_ntoa(ip_src) as 'srcip', layer4_sport as 'srcport', inet_ntoa(ip_dst) as 'dstip',layer4_dport as 'dstport', data_payload as 'data'
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6) t1
where t1.sid=
(select min(t2.sid) from
(select ae.cid as scn,timestamp, inet_ntoa(ip_src) as 'srcip', layer4_sport as 'srcport', inet_ntoa(ip_dst) as 'dstip',layer4_dport as 'dstport', data_payload as 'data'
from acid_event as ae, data as da where ae.cid=da.cid and ae.sid=da.sid and ae.ip_proto =6) t2
where t2.timestamp=t1.timestamp and t2.data=t1.data)