select * from (select distinct * from t where 字段2 in(3,4)) a where (select count(*) from a b where b.字段1=a.字段1)>1
或者 select 字段1,字段2 from t where 字段2 in(3,4) group by 字段1,字段2 having count(字段1)>1
select 字段1,字段2 from tablename where 字段2 in('3','4') group by 字段1,字段2 having count(字段1)>1
sorry,改一下 select * from tablename where 字段1 in ( select 字段1 from tablename where 字段2 in (3,4) group by 字段1 having count(字段1)>1 ) and 字段2 in (3,4)
Select * From (Select BusID,lineid as startid From TB_LineBusFollow Where LineID = 1) as startinner join (Select BusID,lineid as endid From TB_LineBusFollow Where LineID = 22) as endbon start.BusID = endb.BusID busid starid busid endid 1 1 1 22 4 1 4 22 2 1 2 22 109 1 109 22 112 1 112 22 134 1 134 22 142 1 142 22 3 1 3 22 结果正确
select * from t a where 字段2 in(3,4) and exists(select 1 from t where 字段1=a.字段1 and 字段2=3) and exists(select 1 from t where 字段1=a.字段1 and 字段2=4)
--上面几位用group by having ...的没有考虑字段2会重复的情况 --如果数据是: 字段1 字段2 1 4 1 5 1 4--则上面几位的是不对的--正确的应该是这样: select a.* from 表 a,( select 字段1 from 表 where 字段2 in(4,3) group by 字段1 having count(distinct 字段2)=2 )b where a.字段1=b.字段1
select * from t where f1 in ( select f1 from ( select * from t where f1 in (select f1 from t group by f1 having Count(f1)>1) and f2 in (3,4) ) a group by f1 having count(f1)>1 ) and f2 in (3,4)
select x.* from 表x,表 y where x.字段1=y.字段1 and ((x.字段2=3 and y.字段2=4) or (x.字段2=4 and y.字段2=3))
select a.* from 表 a,( select 字段1 from 表 where 字段2 in(4,3) group by 字段1 having count(distinct 字段2)=2 )b where a.字段1=b.字段1
from (select distinct * from t where 字段2 in(3,4)) a
where (select count(*) from a b where b.字段1=a.字段1)>1
select 字段1,字段2
from t
where 字段2 in(3,4)
group by 字段1,字段2
having count(字段1)>1
where 字段2 in('3','4')
group by 字段1,字段2
having count(字段1)>1
select * from tablename where 字段1 in (
select 字段1 from tablename where 字段2 in (3,4) group by 字段1 having count(字段1)>1
) and 字段2 in (3,4)
busid starid busid endid
1 1 1 22
4 1 4 22
2 1 2 22
109 1 109 22
112 1 112 22
134 1 134 22
142 1 142 22
3 1 3 22
结果正确
from t a
where 字段2 in(3,4)
and exists(select 1 from t
where 字段1=a.字段1 and 字段2=3)
and exists(select 1 from t
where 字段1=a.字段1 and 字段2=4)
--如果数据是:
字段1 字段2
1 4
1 5
1 4--则上面几位的是不对的--正确的应该是这样:
select a.*
from 表 a,(
select 字段1 from 表 where 字段2 in(4,3)
group by 字段1
having count(distinct 字段2)=2
)b where a.字段1=b.字段1
(
select f1 from
(
select * from t
where f1 in (select f1 from t group by f1 having Count(f1)>1) and f2 in (3,4)
) a group by f1 having count(f1)>1
) and f2 in (3,4)
from 表 a,(
select 字段1 from 表 where 字段2 in(4,3)
group by 字段1
having count(distinct 字段2)=2
)b where a.字段1=b.字段1