select t1.mid from a t1,a t2 where t1.mid=t2.mid and t1.qsdate=t2.qsdate and t1.status='a' and t2.status='b'
select t1.mid from a t1,a t2 where t1.mid=t2.mid and t1.qsdate=t2.qsdate and ((t1.status='a' and t2.status='b')or (t1.status='b' and t2.status='a'))
/* A表 mid status qsdate 1 a 2006-10-3 2 a 2006-10-4 3 a 2006-10-5 1 b 2006-10-3 找出 status既为a又为b且qsdate相等的mid号*/select mid from t a where exists(select 1 from t where a.mid=mid and status='a' and qsdate=a.qsdate) and a.status='b'
create table #t1 (mid int, status varchar(10), qsdate varchar(10)) insert #t1 select 1, 'a', '2006-10-3' union all select 2, 'a', '2006-10-4' union all select 3, 'a', '2006-10-5' union all select 1, 'b', '2006-10-3' select mid from #t1 a where exists(select 1 from #t1 where a.mid=mid and status='a' and qsdate=a.qsdate) and a.status='b' mid ----------- 1(所影响的行数为 1 行)
select t1.mid from a t1,a t2 where t1.mid=t2.mid and t1.qsdate=t2.qsdate and t1.status='a' and t2.status='b'这个楼上的血的应该能实现
t1.mid
from
a t1,a t2
where
t1.mid=t2.mid and t1.qsdate=t2.qsdate and t1.status='a' and t2.status='b'
select
t1.mid
from
a t1,a t2
where
t1.mid=t2.mid and t1.qsdate=t2.qsdate and ((t1.status='a' and t2.status='b')or (t1.status='b' and t2.status='a'))
/*
A表 mid status qsdate
1 a 2006-10-3
2 a 2006-10-4
3 a 2006-10-5
1 b 2006-10-3 找出 status既为a又为b且qsdate相等的mid号*/select mid from t a
where exists(select 1 from t where a.mid=mid and status='a' and qsdate=a.qsdate)
and a.status='b'
(mid int, status varchar(10), qsdate varchar(10))
insert #t1
select 1, 'a', '2006-10-3' union all
select 2, 'a', '2006-10-4' union all
select 3, 'a', '2006-10-5' union all
select 1, 'b', '2006-10-3' select mid from #t1 a
where exists(select 1 from #t1 where a.mid=mid and status='a' and qsdate=a.qsdate)
and a.status='b'
mid
-----------
1(所影响的行数为 1 行)
t1.mid
from
a t1,a t2 where t1.mid=t2.mid and t1.qsdate=t2.qsdate and t1.status='a' and t2.status='b'这个楼上的血的应该能实现