create table test_table2
(
  id int auto_increment primary key,
  pay_id int,
  pay_time datetime,
  other_col varchar(100)
)

CREATE DEFINER=`root`@`%` PROCEDURE `test_insert`(IN `loopcount` INT)
  LANGUAGE SQL
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  declare cnt int;
  set cnt = 0;
  while cnt< loopcount do
    insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    if (cnt mod 100 = 0) then
      insert into test_table2 (pay_id,pay_time,other_col) values (cnt,date_add(now(), interval floor(300*rand()) day),uuid());
    end if;
    set cnt = cnt + 1;  
  end while;
END

select * from test_table2 force index(idx_pay_id)
where pay_id in (
　　select pay_id from test_table2 
　　where pay_time>="2016-06-01 00:00:00" 
　　　　AND pay_time<="2017-07-03 12:59:59" 
　　group by pay_id 
　　having count(pay_id) > 1
);

select tpp1.* from test_table2 tpp1, 
(
   select pay_id 
   from test_table2 
   WHERE pay_time>="2016-07-01 00:00:00" 
   AND pay_time<="2017-07-03 12:59:59" 
   group by pay_id 
   having count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id

create table test_table2
(
  id int identity(1,1) primary key,
  pay_id int,
  pay_time datetime,
  other_col varchar(100)
)
begin tran
declare @i int = 0
while @i<300000
begin
  insert into test_table2 values (@i,getdate()-rand()*300,newid());
  if(@i00=0)
  begin
    insert into test_table2 values (@i,getdate()-rand()*300,newid());
  end
  set @i = @i + 1
end
COMMIT
GO
create index idx_pay_id on test_table2(pay_id);
create index idx_time on test_table2(pay_time);
GO
select * from test_table2 
where pay_id in (
          select pay_id from test_table2 
          where pay_time>='2017-01-21 00:00:00' 
          AND pay_time<='2017-07-03 12:59:59' 
          group by pay_id 
          having count(pay_id) > 1
        );
select tpp1.* from test_table2 tpp1, 
(
   select pay_id 
   from test_table2 
   WHERE pay_time>='2017-01-21 00:00:00'
   AND pay_time<='2017-07-30 12:59:59' 
   group by pay_id having 
   count(pay_id) > 1
) tpp2 
where tpp1.pay_id=tpp2.pay_id