select distinct c.* from c,cs,s where c.cid = cs.cid and s.sid = cs.sid and s.sname not like '%业务%' 你自己不是已经写出来了吗?
可能我没讲明白吧,我的意思是主表只想用 in 的,因为关联查询十多个表select c.* from c where c.cid in (select cs.cid from cs,s where s.sid = cs.sid and s.sname not like '%业务%' )
if (has.equals("Y")) { hql.append(" AND c.cid in (select cs.cid from cs,s where s.sid = cs.sid and s.sname like '%业务%')"); } else { hql.append(" AND c.cid in (select cs.cid from cs,s where s.sid = cs.sid and s.sname not like '%业务%')"); }我这样做不行
select c.* from c where c.cid in (select cs.cid from cs,s where s.sid = cs.sid and s.sname not like '%业务%' group by cs.cid )
直接使用exists效率会好一点。 select c.* from c where c.cid exists (select 1 from cs,s where s.sid = cs.sid and s.sname not like '%业务%' and c.cid = cs.cid )
where c.cid = cs.cid and s.sid = cs.sid and s.sname not like '%业务%'
你自己不是已经写出来了吗?
where s.sid = cs.sid and s.sname not like '%业务%'
)
hql.append(" AND c.cid in (select cs.cid from cs,s where s.sid = cs.sid and s.sname like '%业务%')");
} else {
hql.append(" AND c.cid in (select cs.cid from cs,s where s.sid = cs.sid and s.sname not like '%业务%')");
}我这样做不行
select c.* from c where c.cid in (select cs.cid from cs,s
where s.sid = cs.sid and s.sname not like '%业务%'
group by cs.cid
)
select c.* from c where c.cid exists (select 1 from cs,s
where s.sid = cs.sid and s.sname not like '%业务%'
and c.cid = cs.cid
)
cid cid sid sid snamec1 c1 s1 s1 业务
c1 c1 s2 s2 其它1
c3 c1 s3 s3 其它2
c4 c1 s4 s4 其它3
c5 c2 s1 s5 其它4
c6 c2 s5 s6 其它5
如上面的表数据