美文网首页
MySql 使用exists查询

MySql 使用exists查询

作者: 小螺丝钉cici | 来源:发表于2018-06-28 14:19 被阅读0次
        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是相对应的。

    相关文章

      网友评论

          本文标题:MySql 使用exists查询

          本文链接:https://www.haomeiwen.com/subject/ufpgyftx.html