【Mysql】查询数据库，行转列，mapper.xml中查询条件的写法

一、用mysql脚本建表

CREATE TABLE IF NOT EXISTS `svt_seventeen`(
   `id` INT not NULL COMMENT '主键',
   `vehicle_id` int NOT NULL COMMENT '车辆id',
   `vehicle_no` VARCHAR(40) NOT NULL COMMENT '车牌号',
   `device_no` VARCHAR(40) COMMENT '设备号',
	 `begin_run_time` datetime COMMENT '开始运行时间',
	 `end_run_time` datetime COMMENT '结束运行时间',
	 `onlineLen` int COMMENT '单次在线时长统计，单位（秒）',
	 `enterprise_id` int COMMENT '车辆所在部门id',
	 `enterprise_name` VARCHAR(255) COMMENT '车辆所在部门名称',
	 `record_date` date COMMENT '记录日期',
	 `create_time` datetime COMMENT '创建时间',
	 `modify_time` datetime COMMENT '修改时间',
	 `delete_state` int not null COMMENT '是否删除，0:否， 1：是',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、现有以下三个实体对应三张表，其关联关系如下

现有三张表：车辆表、用户表、车辆用户关联表。
车辆与用户具备多对多的关系。
需求：展示所有车辆的同时显示有哪些用户关注了该车辆，车辆信息不能重复。

1.车辆表

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("vpn_d_vehicle")
@ApiModel(value="VpnDVehicle对象", description="车辆信息表")
public class VpnDVehicle extends CommonEntity<VpnDVehicle> implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "车辆id")
    @TableId(value = "id", type = IdType.INPUT)
    private Integer id;
    
    @ApiModelProperty(value = "车牌号")
    private String vehicleNo;
}

2.用户表

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("sys_d_user_info")
@ApiModel(value = "SysDUserInfo对象", description = "用户信息表")
public class SysDUserInfo extends CommonEntity<SysDUserInfo> implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "id")
    @TableId(value = "id", type = IdType.INPUT)
    private Integer id;

    @ApiModelProperty(value = "姓名")
    private String userName;
}

3.车辆用户关联表（又名：用户特别关注表，指用户特别关注某些车辆）

@Data
@TableName("vpn_d_special_focus_vehicle")
@ApiModel(value="VpnDSpecialFocusVehicle对象", description="特别关注车辆(绑定用户)表")
public class VpnDSpecialFocusVehicle implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.INPUT)
    private Integer id;

    @ApiModelProperty(value = "车辆id")
    private Integer vehicleId;

    @ApiModelProperty(value = "用户id")
    private Integer userId;
}

三、行转列的sql语句

select t_veh.id as vehID, t_veh.vehicle_no, GROUP_CONCAT(t_focus.user_id) as userId ,  GROUP_CONCAT(t_user.user_name) as userNames 
from vpn_d_vehicle t_veh
left join vpn_d_special_focus_vehicle t_focus on t_veh.id = t_focus.vehicle_id  
LEFT JOIN sys_d_user_info t_user on t_user.id = t_focus.user_id
GROUP BY t_veh.id

查询结果如下：

四、对应的mapper.xml写法

<select id="query" resultType="com.entity.VpnDVehicle">
       select t_veh.id as vehID, t_veh.vehicle_no, GROUP_CONCAT(t_focus.user_id) as userId ,      GROUP_CONCAT(t_user.user_name) as userNames 
       from vpn_d_vehicle t_veh
       left join vpn_d_special_focus_vehicle t_focus on t_veh.id = t_focus.vehicle_id  
       LEFT JOIN sys_d_user_info t_user on t_user.id = t_focus.user_id
       where t_veh.delete_state = 0
       <!--int类型查询条件-->
        <if test="param.vehType != null">
            and t_dic.dic_key = #{param.vehType}
        </if>
       <!--String类型查询条件-->
        <if test="param.vehicleNo != null and param.vehicleNo != ''">
            and t_veh.vehicle_no like concat('%',#{param.vehicleNo},'%')
        </if>
       <!--输入某一关键字，查找车牌号或车名包含该关键字的车辆用or-->
        <if test="param.equipmentNo != null and param.equipmentNo != ''">
            and (t_veh.vehicle_no like concat('%',#{param.equipmentNo},'%')
            or t_veh.veh_name like concat('%',#{param.vehName},'%'))
        </if>
       <!--list类型查询条件-->
        <if test="param.ids != null and param.ids.size() > 0">
            and t_veh.id in
            <foreach collection="param.ids" item="tt" open="(" close=")" separator=",">#{tt}</foreach>
        </if>
        GROUP BY t_veh.id ORDER BY t_veh.id
    </select>

五、输入某一关键字，查找车牌号或车名包含该关键字的车辆用or

if (StringUtils.isNotBlank(vpnDVehicleParam.getEquipmentNo())) {
            lambdaWrapper.and(wrapper -> wrapper
                    .like(VpnDVehicle::getVehicleNo, vpnDVehicleParam.getEquipmentNo())
                    .or()
                    .like(VpnDVehicle::getVehName, vpnDVehicleParam.getEquipmentNo())
            );
        }

六、总结：用GROUP_CONCAT实现行转列

mysql行转列：

select t_veh.id as vehID, t_veh.vehicle_no, GROUP_CONCAT(t_focus.user_id) as userId 
from vpn_d_vehicle t_veh
left join vpn_d_special_focus_vehicle t_focus on t_veh.id = t_focus.vehicle_id  
GROUP BY t_veh.id

注意，使用GROUP_CONCAT时不能掉了group by，否则结果只会有一行。