现在数据库有个字段为重复的次数Num,可能为1,2,3.....。
现在我想查询,把重复记录都显示出来,即查询每条记录,根据后面的重复次数,都重复输出出来。
该如何写sel语句。请高手赐教。
现在我想查询,把重复记录都显示出来,即查询每条记录,根据后面的重复次数,都重复输出出来。
该如何写sel语句。请高手赐教。
解决方案 »
- 大虾们!你们刚开始都是怎么学习Mysql的?
- postgresql 如何批量更新数据库,有这么难吗!!!
- ■■■MYSQL的int型设为自动增量(auto_createment),满了怎么办?
- 批量更改列 字符集
- 请问在网页上如何实现自动显示数据库中的内容?
- MySql数据库之国际化问题
- MYSQL在win2000server的连接;
- 一对多 关联关系的两张表插入数据问题
- 由于后台程序密码忘记了,并且这后台绑定的mysql数据库。我问他说后台程序密码忘记了,不是可以在代码里把后台程序密码重置么,对方说不行,只能进行mysql数据库
- mySql 5.5 怎么存储手机端传过来的各种表情
- 含有blob字段的表查询特别慢,怎么优化SQL?
- 这种条件的查询如何实现:
我是想自己在这个临时表中,直接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个数限制每条记录的个数。