MySql 使用exists查询

2018-06-28  本文已影响0人  小螺丝钉cici
    select a.id,a.reviewer,a.order_id,a.audit_conclusion
    from `heatedloan_credit`.customer_credit_audit a,
    (select max(created_at) as created_at,order_id,reviewer from
    `heatedloan_credit`.customer_credit_audit where audit_status=6 and
    status=0 and exists
    (
    select realname from (
    select realname from heatedloan_manage.cms_security_user where id in (select user_id from
    heatedloan_manage.cms_security_user_role where role_id =18)
    ) as tmp
    )
    GROUP BY order_id order by null) b
    where a.order_id=b.order_id and a.created_at=b.created_at
    and exists  (select order_id from customer_credit_audit  e where  audit_status=7 and status=1)

上面的sql有exists和无exists语句查询结果一样,结果没有改变不是想要的结果。也就是说exists此时要不查询全部,要不没有数据。
将sql改为下面语句就ok了。

    select a.id,a.reviewer,a.order_id,a.audit_conclusion
    from `heatedloan_credit`.customer_credit_audit a,
    (select max(created_at) as created_at,order_id,reviewer from
    `heatedloan_credit`.customer_credit_audit where audit_status=6 and
    status=0 and exists
    (
    select realname from (
    select realname from heatedloan_manage.cms_security_user where id in (select user_id from
    heatedloan_manage.cms_security_user_role where role_id =18)
    ) as tmp
    )
    GROUP BY order_id order by null) b
    where a.order_id=b.order_id and a.created_at=b.created_at
    and exists  (select order_id from customer_credit_audit  e where a.order_id= e.order_id and audit_status=7 and status=1)

相比之下,加了a.order_id= e.order_id 语句。

原理:
1、exists的返回结果是bool型,只有true或者false;内查询中的id,必须为外查询的id。
2、用in实现也一样,不过效率低一些。(此处sql就是因为in耗时达到2s才进行优化,用exists耗时小于1s)
3、exists的效率比in查询要高,因为IN不走索引,但要看实际情况具体使用,IN适合于外表数据量大而内表数据小的情况;exists适合于外表小而内表大的情况。
4、exists与not exists是相对应的。

上一篇下一篇

猜你喜欢

热点阅读