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

约定不等于承诺〃 2024-03-26 19:17 118阅读 0赞

#### 目录 ####

*  一、用mysql脚本建表
 *  二、现有以下三个实体对应三张表，其关联关系如下
 *  三、行转列的sql语句
 *  四、对应的mapper.xml写法
 *  五、输入某一关键字，查找车牌号或车名包含该关键字的车辆用or
 *  六、总结：用GROUP\_CONCAT实现行转列

## 一、用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.车辆表  
![在这里插入图片描述][461cae696333400cb3de6f70e807f555.png]

@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.用户表  
![在这里插入图片描述][4c3a1c3940304186ae92c559c86c6baf.png]

@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.车辆用户关联表（又名：用户特别关注表，指用户特别关注某些车辆）

![在这里插入图片描述][f031467bea6e49fc8b2a7e6421060ba6.png]

@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

查询结果如下：  
![在这里插入图片描述][9b46d59d8573488ba58c71a07e53197a.png]

## 四、对应的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
           
            <if test="param.vehType != null">
                and t_dic.dic_key = #{param.vehType}
            </if>
           
            <if test="param.vehicleNo != null and param.vehicleNo != ''">
                and t_veh.vehicle_no like concat('%',#{param.vehicleNo},'%')
            </if>
           
            <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>
           
            <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，否则结果只会有一行。

[461cae696333400cb3de6f70e807f555.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/6a4430641dde4a5e959259b0a3877c0f.png
[4c3a1c3940304186ae92c559c86c6baf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c4e04c74563349caa664a9817fc78c91.png
[f031467bea6e49fc8b2a7e6421060ba6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c839458e565b4cf293a0ff063e6e4e86.png
[9b46d59d8573488ba58c71a07e53197a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/794f4205fd454c8b8f8c98b86fe5856a.png