一个令人烦恼的sql
遇到一个挺复杂的sql,这个sql会一直执行下去,没有尽头,难道sql也会陷入死循环吗?
select count(*) from scm_order t
left join crm_customer cust on cust.id=t.customer_id
left join sso_person c on c.id=t.creater_id
left join sso_person u on u.id=t.updater_id
left join sso_person a on a.id=t.auditor_id
left join sso_person h on h.id=t.head_id
where t.company_id='0' and (t.create_datetime >= '2015-02-04' or t.sign_date>='2015-02-04')
and (t.create_datetime <= '2015-02-10' or t.sign_date<='2015-02-10') and t.ordertype = '1'
and t.is_deleted = '0' and (t.head_id in(WITH RECURSIVE d AS
(SELECT d1.id,d1.pid,d1.name,d1.sort_num,d1.type FROM v_user_position d1
where d1.id='0'union ALL SELECT d2.id,d2.pid,d2.name,d2.sort_num,d2.type
FROM v_user_position d2, d WHERE d2.pid = d.id)
SELECT distinct id FROM d where type=10 and d.id not in (select id from sso_user
where position_id ='1' and id!='0'))or t.creater_id in
(WITH RECURSIVE d AS (SELECT d1.id,d1.pid,d1.name,d1.sort_num,d1.type
FROM v_user_position d1 where d1.id='0'union ALL SELECT d2.id,d2.pid,d2.name,d2.sort_num,d2.type
FROM v_user_position d2, d WHERE d2.pid = d.id) SELECT distinct id FROM d
where type=10 and d.id not in (select id from sso_user where position_id ='0'and id!='0'))or t.head_id ='0' or t.creater_id='0')