select * from mz_jz where (jjzzbh in (select jjzzbh from mz_jz where jdjzt =1 and jdjlx=1001 group by jjzzbh having count(jjzzbh) > 1)) and jdjzt =1 如何将上述SQL不用in运算符,改用exists运算符,进行查询.
SELECT * FROM mz_jz a WHERE EXISTS (SELECT a FROM mz_jz b WHERE jdjzt = 1 AND jdjlx = 1001 AND b.jjzzbh = a.jjzzbh GROUP BY jjzzbh HAVING COUNT (jjzzbh) > 1) AND jdjzt = 1
这个不对呀,子查询里怎么会select a?
是不是应该是select * 或者 jjzzbh 我改成了select *from mz_jz b 不行的,速度很慢
select * from mz_jz where exists ( select jjzzbh from mz_jz where jdjzt=1 and jdjlx=1001 group by jjzzbh having count(jjzzbh) > 1 ) and jdjzt = 1
select * from mz_jz a where exists ( select jjzzbh from mz_jz b where jdjzt=1 and jdjlx=1001 and a.jjzzbh = b.jjzzbh group by jjzzbh having count(jjzzbh) > 1 ) and jdjzt = 1
SELECT a 在exists里,这个字段随便写的,只是判断有没有这个输出结果,你用select *就会慢很多的, 这样只要有结果就输出A exists也就为真
SELECT *
FROM mz_jz a
WHERE EXISTS (SELECT a
FROM mz_jz b
WHERE jdjzt = 1 AND jdjlx = 1001 AND b.jjzzbh = a.jjzzbh
GROUP BY jjzzbh
HAVING COUNT (jjzzbh) > 1)
AND jdjzt = 1
*
from mz_jz
where exists
(
select
jjzzbh
from mz_jz
where jdjzt=1 and jdjlx=1001
group by jjzzbh having count(jjzzbh) > 1
)
and jdjzt = 1
*
from mz_jz a
where exists
(
select
jjzzbh
from mz_jz b
where jdjzt=1 and jdjlx=1001 and a.jjzzbh = b.jjzzbh
group by jjzzbh having count(jjzzbh) > 1
)
and jdjzt = 1
在exists里,这个字段随便写的,只是判断有没有这个输出结果,你用select *就会慢很多的,
这样只要有结果就输出A
exists也就为真