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:分组查询并显示查询出来的结果