目录

• 一、用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.车辆表

@Data
@EqualsAndHashCode(callSuper = true)
@TableName("vpn_d_vehicle")
@ApiModel(value="VpnDVehicle对象", description="车辆信息表")
public class VpnDVehicle extends CommonEntity 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 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 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_vehleft 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_idwhere t_veh.delete_state = 0and t_dic.dic_key = #{param.vehType}and t_veh.vehicle_no like concat('%',#{param.vehicleNo},'%')and (t_veh.vehicle_no like concat('%',#{param.equipmentNo},'%')or t_veh.veh_name like concat('%',#{param.vehName},'%')) 0">and t_veh.id in#{tt}GROUP BY t_veh.id ORDER BY t_veh.id

五、输入某一关键字，查找车牌号或车名包含该关键字的车辆用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，否则结果只会有一行。