sql语句多表联查

复杂sql学习，方便写出骚sql。

SELECT
        t.create_time as createTime,
        t.archives_no as dataId,
        v.Frame_number as frameNumber,
        v.plate_number as plateNumber,
        v.engine_no as engineNumber,
        v.registration as registerNumber,
        v.label_code as carLabel,
        c.`name` as carType ,
        case when t.bill_status = '02' then '已交易'
        else '未交易'
        END as txnStatus,
        t.create_time as inDate,
        t.out_time as outDate,
        su.name as sellerName,
        su.cert_code as sellerCertNumber,
        su.phone as sellerPhone,
        bu.name as buyerName,
        bu.cert_code as buyerCertNumber,
        bu.phone as buyerPhone,
        u.user_name as merchantName,
        u.telephone as merchantPhone,
        t.bill_no as billNumber,
        case when t.bill_type = '02' then '重开 （新）'
         when t.bill_type = '03' then '退票（旧）'
         when t.bill_type = '04' then '收费换票（旧）'
         when t.bill_type = '05' then '免费换票（旧）'
         else '正常'
        END as billType,
        t.sell_price as txnPrice,
        b.bill_date as billDate,
        b.bill_code as billCode,
        v.vehicle_manage as inAddress,
        d.address as merchantAddress
    FROM
        tg_trans_record t
        LEFT JOIN tg_vehicle v ON t.vehicle = v.id_
        LEFT JOIN tg_codeitem c ON v.Vehicle_type = c.`code`
        AND c.codemap = 'Vehicle_type'
        left join tg_biz_user su on t.seller = su.id_
        left join tg_biz_user bu on t.seller = bu.id_
        left join tg_user u on u.id_ = t.agency_staff
        left join tg_bill b on b.bill_no = t.bill_no
        left join tg_dealers d on d.login_name = u.login_name LIMIT 1,1

 <select id="getHandleCaseInfo" resultType="com.iflytek.sf.zlfy.model.vo.CaseInfoVo">
        SELECT
        c.case_order_no AS caseOrderNo,
        c.apply_no AS applyNo,
        c.case_no AS caseNo,
        c.case_name AS caseName,
        c.case_cause AS caseCause,
        c.case_type AS caseType,
        c.case_status AS caseStatus,
        c.apply_type AS applyType,
        IF( hu.sort = 1, 1, 0 ) AS isMainHandleUser,
        c.receive_file_time AS receiveFileTime,
        c.sure_time AS sureTime,
        c.is_simple AS isSimple,
        c.start_time AS startTime,
        c.deadline_time AS deadlineTime,
        c.correction_status AS correctionStatus,
        c.pause_status AS pauseStatus,
        c.postpone_status AS postponeStatus,
        ( SELECT GROUP_CONCAT( aa.applicant_name ORDER BY aa.sort ) AS applicantNames FROM t_applicant_info aa WHERE
        aa.case_order_no = c.case_order_no ) AS applicantNames,
        ( SELECT GROUP_CONCAT( rr.respondent_name ORDER BY rr.sort ) AS respondentNames FROM t_respondent_info rr WHERE
        rr.case_order_no = c.case_order_no ) AS respondentNames,
        <include refid="com.iflytek.sf.zlfy.mapper.CommonMapper.getTimeLimit"/>
        AS timeLimit,
        IF( c.case_status in (4, 5, 6), IF( c.correction_status = 2, IF( c.pause_status = 2, '补正中、中止中', '补正中' ), IF(
        c.pause_status = 2, '中止中', NULL ) ), '--' ) AS timeLimitStr,
        <include refid="com.iflytek.sf.zlfy.mapper.CommonMapper.getTimeLimitSort"/>
        AS timeLimitSort,
        cl.close_time AS closeTime
        FROM t_case_info c
        LEFT JOIN (SELECT huser.* FROM t_case_handle_user huser WHERE huser.case_order_no = #{caseOrderNo} ORDER BY
        huser.sort limit 1) hu ON c.case_order_no = hu.case_order_no
        LEFT JOIN t_case_close cl ON c.case_order_no = cl.case_order_no
        LEFT JOIN (SELECT tc.case_order_no, tc.end_time FROM
        (SELECT tc.case_order_no,MAX(tc.create_time) AS create_time FROM t_case_correction tc GROUP BY tc.case_order_no)
        t
        LEFT JOIN t_case_correction tc ON t.case_order_no = tc.case_order_no AND tc.create_time = t.create_time ) tc ON
        tc.case_order_no = c.case_order_no
        where c.case_order_no = #{caseOrderNo,jdbcType=VARCHAR} and is_delete = 0
        <if test="userId != null and userId != ''">
            AND hu.handle_user_id = #{userId}
        </if>
        group by c.case_order_no
    </select>

  <sql id="getTimeLimit">
        <!-- 当案件处于补正中状态，则审限停止计算 -->
        CASE
        WHEN c.correction_status = 2 THEN '--'
        WHEN c.pause_status = 2 THEN '--'
        <!-- 当案件处于完成补正状态，则审限从恢复当天开始重新计算 -->
        WHEN c.correction_status = 3 AND tc.end_time IS NOT NULL THEN
            CASE
            <!-- 当天不展示审限 -->
            WHEN DATEDIFF(tc.end_time, CURDATE()) = 0 THEN '--'
            <!-- 针对登记和受理审查阶段案件，审限为5个工作日，申诉审查和复议审查阶段，为60个自然日 -->
            WHEN c.case_status IN (3) THEN
                getAcceptRD(CURDATE(), tc.end_time, 5)
            <!-- 结案归档的不展示 -->
            WHEN c.case_status IN (7) THEN '--'
            ELSE
                CASE
                WHEN c.deadline_time IS NOT NULL THEN
                    IF( DATEDIFF(c.deadline_time, CURDATE()) = 0, -1, DATEDIFF(c.deadline_time, CURDATE()) )
                ELSE
                    '--'
                END
            END
        ELSE
            <!-- 当案件未进行补正，则审限从收案日期当天开始计算  -->
            <!-- 当天不展示审限 -->
            CASE
            WHEN DATEDIFF(c.start_time, CURDATE()) = 0 THEN '--'
            <!-- 针对登记和受理审查阶段案件，审限为5个自然日，申诉审查和复议审查阶段，为60个自然日 -->
            WHEN c.case_status IN (3) THEN
                getAcceptRD(CURDATE(), c.start_time, 5)
            <!-- 结案归档的不展示 -->
            WHEN c.case_status IN (7) THEN '--'
            ELSE
                CASE
                WHEN c.deadline_time IS NOT NULL THEN
                    IF( DATEDIFF(c.deadline_time, CURDATE()) = 0, -1, DATEDIFF(c.deadline_time, CURDATE()) )
                ELSE
                    '--'
                END
            END
        END
    </sql>


<sql id="getTimeLimitSort">
        <!-- 当案件处于补正中、中止中状态，则排在最后 -->
        CASE
        WHEN c.correction_status = 2 THEN 99999
        WHEN c.pause_status = 2 THEN 99999
        <!-- 当案件处于完成补正状态，则审限从补正恢复当天开始重新计算 -->
        WHEN c.correction_status = 3 AND tc.end_time IS NOT NULL THEN
            CASE
            <!-- 当天不展示审限，排在最后 -->
            WHEN DATEDIFF(tc.end_time, CURDATE()) = 0 THEN 99999
            <!-- 针对登记阶段案件，审限为5个工作日，受理和办理阶段，为60个自然日 -->
            WHEN c.case_status IN (4) THEN
                getAcceptRD(CURDATE(), tc.end_time, 5)
            <!-- 结案归档的不展示 -->
            WHEN c.case_status IN (7) THEN 99999
            ELSE
                CASE
                WHEN c.deadline_time IS NOT NULL THEN
                    IF(DATEDIFF(c.deadline_time, CURDATE()) = 0, -1, DATEDIFF(c.deadline_time, CURDATE()))
                ELSE
                    99999
                END
            END
        ELSE
            <!-- 当案件未处于补正中、中止中状态，则审限从审限计算开始日期当天开始计算  -->
            <!-- 当天不展示审限，排在最后 -->
            CASE
            WHEN DATEDIFF(c.start_time, CURDATE()) = 0 THEN 99999
            <!-- 针对登记阶段案件，审限为5个工作日，受理和办理阶段，为60个自然日 -->
            WHEN c.case_status IN (4) THEN
                getAcceptRD(CURDATE(), c.start_time, 5)
            <!-- 结案归档的不展示 -->
            WHEN c.case_status IN (7) THEN 99999
            ELSE
                CASE
                WHEN c.deadline_time IS NOT NULL THEN
                    IF(DATEDIFF(c.deadline_time, CURDATE()) = 0, -1, DATEDIFF(c.deadline_time, CURDATE()))
                ELSE
                    99999
                END
            END
        END
    </sql>