前言
小编最近在项目中接触到一个超长的SQL语句,发现里面的语法很丰富,可以拿出来记录一下,当然也有将其中重复的部分进行删减,方便博友们进行查看分析。
展示SQL语句
SQL语句查询,作用是从两张表 t_card_order 和 t_card_consume_info 中查询出各自需要的字段信息。
SELECT
p.pay_time AS paymentCreateTime,
p.thirdpart_trans_code AS thirdpartTransCode,
o.id AS orderCode,
'购卡' AS payType,
CASE o.card_type
WHEN '1' THEN '储值卡'
WHEN '2' THEN '权益卡'
END AS cardType
FROM t_card_order o
RIGHT JOIN t_order_payment p
ON o.id = p.order_code
LEFT JOIN t_movie_cinema c
ON o.cinema_code = c.cinema_code
WHERE
<if test="param.cinemaCodes != null">
AND o.cinema_code in
<foreach item="item" index="index" collection="param.cinemaCodes" open="
(" separator="," close=")">
#{item}
</foreach>
</if>
<choose>
<when test="param.dateTag == 'week'">
AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(p.pay_time)
</when>
<when test="param.dateTag == 'oneMonth'">
AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= DATE(p.pay_time)
</when>
<when test="param.dateTag == 'threeMonth'">
AND DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= DATE(p.pay_time)
</when>
</choose>
UNION
SELECT
info.create_time AS paymentCreateTime,
info.transaction_id AS thirdpartTransCode,
info.id AS orderCode,
'充值' AS payType,
CASE info.card_type
WHEN '1' THEN '储值卡'
WHEN '2' THEN '权益卡'
END AS cardType
FROM t_card_consume_info info
LEFT JOIN t_movie_cinema c
ON info.cinema_code = c.cinema_code
WHERE info.consume_type = 0
AND consume_comment != '购卡'
AND consume_comment != '绑卡首次充值'
分析SQL语句
一、union语句
注意:使用union语句,联结的两张表之间的字段个数、名称必须保持一致,否则会报错
-- 第一张表中的字段如下:
p.pay_time AS paymentCreateTime,
p.thirdpart_trans_code AS thirdpartTransCode,
o.id AS orderCode,
'购卡' AS payType,
CASE o.card_type
WHEN '1' THEN '储值卡'
WHEN '2' THEN '权益卡'
END AS cardType
-- 第二张表中的字段如下:
info.create_time AS paymentCreateTime,
info.transaction_id AS thirdpartTransCode,
info.id AS orderCode,
'充值' AS payType,
CASE info.card_type
WHEN '1' THEN '储值卡'
WHEN '2' THEN '权益卡'
END AS cardType
二、条件语句
三种条件语句:case、if和choose whenti
---case条件语句
CASE o.card_type
WHEN '1' THEN '储值卡'
WHEN '2' THEN '权益卡'
END AS cardType
---if条件语句
<if test="param.cinemaCodes != null">
AND o.cinema_code in
<foreach item="item" index="index" collection="param.cinemaCodes" open="
(" separator="," close=")">
#{item}
</foreach>
</if>
---choose when条件语句
<choose>
<when test="param.dateTag == 'week'">
AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(p.pay_time)
</when>
<when test="param.dateTag == 'oneMonth'">
AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= DATE(p.pay_time)
</when>
<when test="param.dateTag == 'threeMonth'">
AND DATE_SUB(CURDATE(), INTERVAL 3 MONTH) <= DATE(p.pay_time)
</when>
</choose>
三、查询结果图
字段 为null值: orderCode、cardType等,产生的原因是左右外连接的结果:left join / right join
小结
通过一番总结,能够让自己对于SQL语句的查询有更深刻的认识,同时也希望对大家有所帮助!
感谢您的访问!