我是在存储过程中使用的。源数据表有字段(SIP,SPORT,DIP,DPORT,Flag)。其中Flag为0或1,其他字段都可能相同。建一个临时表,首先使用GROUB BY SIP,SPORT,DIP,DPORT where Flag=1, 算出Flag=1个数a,再GROUB BY SIP,SPORT,DIP,DPORT where Flag=0 个数b。根据a-b的差值,打印该记录个数。 我是想自己在这个临时表中,直接select,并能根据a-b的差值打印出重复记录就好了,就是不知道sql查询怎么写。如何这样不行,那根据临时表中的a-b的值和SIP,SPORT,DIP,DPORT字段的信息,再返回来再临时表中查询也行。但是根据a-b的值,如何在源表查询时,限制每条符合记录的个数呢。 比如临时表中一条记录为SIP,SPORT,DIP,DPORT,a-b=2.那我就要根据2和SIP,SPORT,DIP,DPORT,查询得到两条符合条件的记录。我该如何处理呢,多谢赐教
select 有个字段,sum(Flag) as a,sum(1-Flag) as b from 源数据表 group by 有个字段
源表建表就不用贴了吧,我把我的存储过程贴出来一部分吧。set @j=concat(' create temporary table if not exists test (ClientIP varchar(16), ServerIP varchar(16), ClientPort int(11) default 0, ServerPort int(11) default 0, StartNum int(11) default 0, EndNum int(11) default 0, Diff int(11) default 0, primary key (ClientIP,ServerIP,ClientPort,ServerPort) )'); prepare stmtj from @j; execute stmtj; deallocate prepare stmtj; TRUNCATE TABLE test;set @a=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,StartNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =1 and Time<="',startTime,'GROUP by ClientIP, ServerIP,ClientPort,ServerPort'); prepare stmta from @a; execute stmta; deallocate prepare stmta;set @b=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,EndNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =0 and Time<="',startTime,' GROUP by ClientIP, ServerIP,ClientPort,ServerPort'); prepare stmtb from @b; execute stmtb; deallocate prepare stmtb;update test set Diff=StartNum - EndNum where StartNum - EndNum >0;所以在临时表中得到(ClientIP, ServerIP,ClientPort,ServerPort,Diff)下面我想根据Diff个数,再在源表中查询相应的数据,并根据Diff个数限制每条记录的个数。
select col,count(*) from tb group by col having count(*)>1
源表建表就不用贴了吧,我把我的存储过程贴出来一部分吧。 set @j=concat(' create temporary table if not exists test (ClientIP varchar(16), ServerIP varchar(16), ClientPort int(11) default 0, ServerPort int(11) default 0, StartNum int(11) default 0, EndNum int(11) default 0, Diff int(11) default 0, primary key (ClientIP,ServerIP,ClientPort,ServerPort) )'); prepare stmtj from @j; execute stmtj; deallocate prepare stmtj; TRUNCATE TABLE test;set @a=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,StartNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =1 and Time<="',startTime,'GROUP by ClientIP, ServerIP,ClientPort,ServerPort'); prepare stmta from @a; execute stmta; deallocate prepare stmta;set @b=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,EndNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =0 and Time<="',startTime,' GROUP by ClientIP, ServerIP,ClientPort,ServerPort'); prepare stmtb from @b; execute stmtb; deallocate prepare stmtb;update test set Diff=StartNum - EndNum where StartNum - EndNum >0; 所以在临时表中得到(ClientIP, ServerIP,ClientPort,ServerPort,Diff)下面我想根据Diff个数,再在源表中查询相应的数据,并根据Diff个数限制每条记录的个数。
我是想自己在这个临时表中,直接select,并能根据a-b的差值打印出重复记录就好了,就是不知道sql查询怎么写。如何这样不行,那根据临时表中的a-b的值和SIP,SPORT,DIP,DPORT字段的信息,再返回来再临时表中查询也行。但是根据a-b的值,如何在源表查询时,限制每条符合记录的个数呢。 比如临时表中一条记录为SIP,SPORT,DIP,DPORT,a-b=2.那我就要根据2和SIP,SPORT,DIP,DPORT,查询得到两条符合条件的记录。我该如何处理呢,多谢赐教
from 源数据表
group by 有个字段
create temporary table if not exists test
(ClientIP varchar(16),
ServerIP varchar(16),
ClientPort int(11) default 0,
ServerPort int(11) default 0,
StartNum int(11) default 0,
EndNum int(11) default 0,
Diff int(11) default 0,
primary key (ClientIP,ServerIP,ClientPort,ServerPort)
)'); prepare stmtj from @j;
execute stmtj;
deallocate prepare stmtj;
TRUNCATE TABLE test;set @a=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,StartNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =1 and Time<="',startTime,'GROUP by ClientIP, ServerIP,ClientPort,ServerPort');
prepare stmta from @a;
execute stmta;
deallocate prepare stmta;set @b=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,EndNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =0 and Time<="',startTime,' GROUP by ClientIP, ServerIP,ClientPort,ServerPort');
prepare stmtb from @b;
execute stmtb;
deallocate prepare stmtb;update test set Diff=StartNum - EndNum where StartNum - EndNum >0;所以在临时表中得到(ClientIP, ServerIP,ClientPort,ServerPort,Diff)下面我想根据Diff个数,再在源表中查询相应的数据,并根据Diff个数限制每条记录的个数。
from tb
group by col
having count(*)>1
set @j=concat('
create temporary table if not exists test
(ClientIP varchar(16),
ServerIP varchar(16),
ClientPort int(11) default 0,
ServerPort int(11) default 0,
StartNum int(11) default 0,
EndNum int(11) default 0,
Diff int(11) default 0,
primary key (ClientIP,ServerIP,ClientPort,ServerPort)
)'); prepare stmtj from @j;
execute stmtj;
deallocate prepare stmtj;
TRUNCATE TABLE test;set @a=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,StartNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =1 and Time<="',startTime,'GROUP by ClientIP, ServerIP,ClientPort,ServerPort');
prepare stmta from @a;
execute stmta;
deallocate prepare stmta;set @b=concat('insert into test(ClientIP, ServerIP,ClientPort,ServerPort,EndNum) select ClientIP,ServerIP,ClientPort,ServerPort from SourceTable where Flag =0 and Time<="',startTime,' GROUP by ClientIP, ServerIP,ClientPort,ServerPort');
prepare stmtb from @b;
execute stmtb;
deallocate prepare stmtb;update test set Diff=StartNum - EndNum where StartNum - EndNum >0;
所以在临时表中得到(ClientIP, ServerIP,ClientPort,ServerPort,Diff)下面我想根据Diff个数,再在源表中查询相应的数据,并根据Diff个数限制每条记录的个数。