在数据库操作中，我们经常遇到需要根据字段的存在与否动态选择返回值的场景。本文通过一个具体的例子，展示如何使用SQL语句来优化这种情况的处理，确保我们的查询结果既灵活又高效。
背景
假设我们有一个关于车票购买记录的数据库，表cz_ticket存储了票务信息，表sys_user存储了用户信息。我们需要从cz_ticket表中检索票务信息，同时关联sys_user表来获取购票者的昵称。如果用户没有设置昵称，则需要默认使用他们的用户名。
SQL查询需求
我们的目标是构建一个SQL查询，它不仅能够根据多个条件过滤数据，如区段起始、结束点、购票日期和车牌号，还能够处理昵称为空的情况，此时应选择用户名作为备选显示名。
实现步骤
-
关联表查询：首先，我们使用
LEFT JOIN来关联cz_ticket和sys_user表，以确保即使某些票务记录没有对应的用户信息也能被检索到。 -
条件筛选：使用
WHERE语句和MyBatis的<if>标签动态插入条件，这样可以根据实际传入的参数来调整查询条件，提高查询的灵活性和效率。 -
动态字段选择：使用
COALESCE函数来选择nick_name和create_name中非空的那一个作为返回结果的display_name。 -
排序逻辑：特定字段
gate_no的排序逻辑，确保按照特定格式进行排序，以适应业务需求。
完整的SQL语句
SELECT t.*,
COALESCE(u.nick_name, t.create_name) AS display_name
FROM cz_ticket t
LEFT JOIN sys_user u ON t.create_name = u.user_name
WHERE 1=1
<if test="form.sectionStart!=null and form.sectionStart!=''"> AND section_start = #{form.sectionStart}</if>
<if test="form.sectionEnd!=null and form.sectionEnd!=''"> AND section_end = #{form.sectionEnd}</if>
<if test="form.startDate!=null and form.startDate!=''"> AND ticket_buy_date >= #{form.startDate}</if>
<if test="form.endDate!=null and form.endDate!=''"> AND ticket_buy_date <= #{form.endDate}</if>
<if test="form.licensePlate!=null and form.licensePlate!=''"> AND license_plate = #{form.licensePlate}</if>
<if test="form.nickName!=null and form.nickName!=''"> AND u.nick_name LIKE CONCAT('%', #{form.nickName}, '%')</if>
ORDER BY CASE WHEN gate_no LIKE '天-%' THEN 2 ELSE 1 END,
CAST(SUBSTRING_INDEX(gate_no, '-', 1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(gate_no, '-', -1) AS UNSIGNED);
结论
使用COALESCE函数在SQL查询中实现字段的动态选择，不仅增加了查询的灵活性，还保证了数据的完整性和准确性。这种方法适用于多种业务场景，尤其是在用户界面显示信息需要根据数据库中数据的存在与否动态调整时。