mysql关联表查询

select h.id,h.eventId,h.patrolDate,h.eventName,h.location,h.originName,h.assignName,h.rehappen,
        IFNULL(h.stateName, '待下达') as patrolState,//判断h.stateName是否有值
        h.depName,h.userName,h.executorName,h.executorDepart
        from
          (select
            g.*,
            s3.proName as stateName,u2.userName as userName,d1.depName as depName,u3.userName as executorName,
            d2.depName as executorDepart
           from (
            SELECT c1.*,
            (CASE
            WHEN c1.isrehappen = 0 THEN
            '不重复发生'
            WHEN c1.isrehappen = 1 THEN
            '重复发生'
            END)  as rehappen ,   //相当于switch或者if else
            DATE_FORMAT(c1.patrolTime,'%Y-%m-%d %H:%i:%S') patrolDate,//把时间转成字符串类型
            c2.state as taskState,s1.proName as eventName,s2.proName as originName,u1.userName as assignName,
            trim(both ',' from c2.executor) as executorId //截取掉首尾的逗号
            FROM gl_patrol c1
            LEFT JOIN gl_task c2 ON c1.id =  c2.FixedId
            left join s_basedata s1 on  c1.event = s1.id
            left join s_basedata s2 on  c1.complaintOrigin = s2.id
            left join s_user u1 on  c2.userId = u1.userId
            <where>
                <if test="theType != null and theType != '' and theType == 0">
                    and c1.eventState = #{theType}
                </if>
                <if test="theType != null and theType != '' and theType > 0">
                    and c2.state = #{theType}
                </if>
                <if test="eventId != null and eventId != ''">
                    and c1.eventId like CONCAT('%',#{eventId},'%' ) //like的用法
                </if>
                <if test="starttime != null and starttime != ''">
                    <![CDATA[and SUBSTRING(c1.patrolTime,1,10)>=#{starttime}]]>
                </if>
                <if test="endtime != null and endtime != ''">
                    <![CDATA[and SUBSTRING(c1.patrolTime,1,10)<=#{endtime}]]>
                </if>
                <if test="Type != null  and Type!=0 ">
                    AND c1.type = #{Type}
                </if>
                <if test="midType != null  and midType!=0 ">
                    AND c1.midType = #{midType}
                </if>
                <if test="Event != null  and Event != 0 ">
                    AND c1.event =  #{Event}
                </if>
                <if test="presentation != null  and presentation !='' ">
                    AND c1.presentation like CONCAT('%',#{presentation},'%' )
                </if>
                <if test="Area != null and Area != ''">
                    and c1.Area = #{Area}
                </if>
                <if test="complaintOrigin != null  and complaintOrigin !='' ">
                    AND c1.complaintOrigin =  #{complaintOrigin}
                </if>
                <if test="telephone != null and telephone !='' ">
                    AND c1.telephone like CONCAT('%',#{telephone},'%')
                </if>
                <if test="userId != null  and userId !='' ">
                    AND c1.userId = #{userId}
                </if>
            </where>
          )g
          left join s_basedata s3 on g.taskState = s3.id
          left join s_user u2 on g.userId = u2.userId
          left join s_user u3 on g.executorId = u3.userId
          left join s_department d1 on u2.departmentId = d1.id
          left join s_department d2 on u3.departmentId = d2.id
      )h

SQL语句感觉写的不行，大佬看了不要笑我，希望大佬看了有所赐教