select t1.* from tb where No in
(
select distinct t1.No from
(select * from tb where type = 2) t1,
(select * from tb where type = 1) t2
where t1.no = t2.no and t1.date < t2.date
)
(
select distinct t1.No from
(select * from tb where type = 2) t1,
(select * from tb where type = 1) t2
where t1.no = t2.no and t1.date < t2.date
)
insert into tb values('101', '12/7', 100 , 2 )
insert into tb values('102', '12/7', 110 , 1 )
insert into tb values('103', '12/7', 120 , 2 )
insert into tb values('102', '12/8', 90 , 2 )
insert into tb values('101', '12/8', 200 , 1 )
insert into tb values('103', '12/8', 150 , 1 )
goselect t.* from tb t where type = 2 and No in
(
select distinct t1.No from
(select * from tb where type = 2) t1,
(select * from tb where type = 1) t2
where t1.no = t2.no and t1.date < t2.date
)drop table tb/*
No date money type
---------- ---------- ----------- -----------
101 12/7 100 2
103 12/7 120 2(所影响的行数为 2 行)
*/