复杂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>