mysql查询时,把一列中的数据多列显示

mysql查询时,把一列中的数据多列显示

在进行多表查询时,把一列中的数据多列显示,可以使用子查询的方式:

最近在项目中遇到的问题:

表busic_project_review(评审人表)为主表,表busi_project(项目表)为从表

遇到的情况:主表与从表是一对多的关系,查询busi_project_review表中的review_user_name(评审人姓名)字段,并
且根据review_level(评审级别)的值显示,一个项目有三个评审级别,每个级别可以有多个人

sql语句:
select
a.project_id as projectId,c.platform_id as platformId,c.platform_name as platformName,d.car_type_id as carTypeId,
d.car_type_name as carTypeName,b.project_level as projectLevel,b.project_code as projectCode,b.project_name as projectName,
b.project_manager_id as projectManagerId,b.project_manager_name as projectManagerName,b.project_description as projectDescription,
b.project_status as projectStatus,a.review_level as reviewLevel,DATE_FORMAT(b.project_start_time,’%Y-%m-%d’) as projectStartTime,
DATE_FORMAT(b.project_end_time,’%Y-%m-%d’) as projectEndTime,
(select GROUP_CONCAT(a.review_user_name) from busi_project_review a where a.review_level=“1” and a.project_id=b.project_id) as review_user_name1,
(select GROUP_CONCAT(a.review_user_name) from busi_project_review a where a.review_level=“2” and a.project_id=b.project_id) as review_user_name2,
(select GROUP_CONCAT(a.review_user_name) from busi_project_review a where a.review_level=“3” and a.project_id=b.project_id) as review_user_name3
from busi_project_review a
left join busi_project b on b.project_id=a.project_id
left join dict_platform c on c.platform_id=b.platform_id
left join dict_car_type d on d.car_type_id=b.car_type_id
where a.statesign=0
GROUP BY a.project_id

重点是:(select GROUP_CONCAT(a.review_user_name) from busi_project_review a where a.review_level=“1” and a.project_id=b.project_id) as review_user_name1,

GROUP_CONCAT:分组查询并显示查询出来的结果

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值