56,687
社区成员
发帖
与我相关
我的任务
分享
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;
select 有个字段,sum(Flag) as a,sum(1-Flag) as b
from 源数据表
group by 有个字段