今天入职,给我做了个字段的CRUD比较简单,到了下午,做了一个根据2个时间字段查询 内容的功能 使用 between 解决了,但是解决过程中表示看到公司大佬写的一大推sql语句瑟瑟发抖!!
超长预警啊!!仔细分析了其实还是很简单的,我做的就是添加一个动态sql
利用前台传入的2个时间字段查询内容,唉心累啊~~~,sql基础十分不扎实,先看了起码3个小时都是懵逼的,导致公司技术大佬不想叼我了。期待第二天看看会不会去世
<if test="pd.lastLoginStart!= null and pd.lastLoginEnd!= null and pd.lastLoginEnd != ''"><!-- 关键词检索 -->
AND substr(a.TIMES,1,11) BETWEEN #{pd.lastLoginStart} AND #{pd.lastLoginEnd}
</if>
select * from
(SELECT
a.ID,
a.CARD_NUMBER,
a.NUMBER,
a.NUMBER_PERIOD,
p.NAMES AS PERIOD_NAME,
a.NUMBER_CLASSIFICATION,
a.NUMBER_TYPE,
t.NAMES AS TYPE_NAME,
a.OPEN_COMPANY,
a.USE_COMPANY,
a.PURPOSE,
a.TIMES,
a.ISDELETE,
a.DATA_FROM_EXCEL
from
<include refid="tableName"></include> a
left join ch_num_period p on a.NUMBER_PERIOD = p.ID
left join ch_num_type t on a.NUMBER_TYPE = t.ID
where a.DATA_FROM_EXCEL = 0
and a.ISDELETE = '0'
<if test="pd.lastLoginStart!= null and pd.lastLoginEnd!= null and pd.lastLoginEnd != ''"><!-- 关键词检索 -->
AND substr(a.TIMES,1,11) BETWEEN #{pd.lastLoginStart} AND #{pd.lastLoginEnd}
</if>
<if test="pd.keywords!= null and pd.keywords != ''"><!-- 关键词检索 -->
and
(
a.CARD_NUMBER LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.NUMBER LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.OPEN_COMPANY LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.USE_COMPANY LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.PURPOSE LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
)
</if>
<if test="pd.NUMBER_PERIOD!= null and pd.NUMBER_PERIOD != ''"><!-- 关键词检索 -->
and a.NUMBER_PERIOD = #{pd.NUMBER_PERIOD}
</if>
<if test="pd.NUMBER_CLASSIFICATION!= null and pd.NUMBER_CLASSIFICATION != ''"><!-- 关键词检索 -->
and a.NUMBER_CLASSIFICATION = #{pd.NUMBER_CLASSIFICATION}
</if>
<if test="pd.NUMBER_TYPE!= null and pd.NUMBER_TYPE != ''"><!-- 关键词检索 -->
and a.NUMBER_TYPE = #{pd.NUMBER_TYPE}
</if>
UNION ALL
SELECT
a.ID,
a.CARD_NUMBER,
a.NUMBER,
a.NUMBER_PERIOD,
a.NUMBER_PERIOD AS PERIOD_NAME,
a.NUMBER_CLASSIFICATION,
a.NUMBER_TYPE,
a.NUMBER_TYPE AS TYPE_NAME,
a.OPEN_COMPANY,
a.USE_COMPANY,
a.PURPOSE,
a.STATE,
a.ISDELETE,
a.DATA_FROM_EXCEL,
a.TIMES
from
<include refid="tableName"></include> a
left join ch_num_period p on a.NUMBER_PERIOD = p.ID
left join ch_num_type t on a.NUMBER_TYPE = t.ID
where a.DATA_FROM_EXCEL = 1
and a.ISDELETE = '0'
<if test="pd.lastLoginStart!= null and pd.lastLoginEnd!= null and pd.lastLoginEnd != ''"><!-- 关键词检索 -->
AND substr(a.TIMES,1,11) BETWEEN #{pd.lastLoginStart} AND #{pd.lastLoginEnd}
</if>
<if test="pd.keywords!= null and pd.keywords != ''"><!-- 关键词检索 -->
and
(
a.CARD_NUMBER LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.NUMBER LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.NUMBER_PERIOD LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.NUMBER_CLASSIFICATION LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.NUMBER_TYPE LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.OPEN_COMPANY LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.USE_COMPANY LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
or
a.PURPOSE LIKE CONCAT(CONCAT('%', #{pd.keywords}),'%')
)
</if>
<if test="pd.NUMBER_PERIOD!= null and pd.NUMBER_PERIOD != ''"><!-- 关键词检索 -->
and a.NUMBER_PERIOD = #{pd.NUMBER_PERIOD}
</if>
<if test="pd.NUMBER_CLASSIFICATION!= null and pd.NUMBER_CLASSIFICATION != ''"><!-- 关键词检索 -->
and a.NUMBER_CLASSIFICATION = #{pd.NUMBER_CLASSIFICATION}
</if>
<if test="pd.NUMBER_TYPE!= null and pd.NUMBER_TYPE != ''"><!-- 关键词检索 -->
and a.NUMBER_TYPE = #{pd.NUMBER_TYPE}
</if>
) temp
order by temp.TIMES desc