MYSQL查询以数组形式返回将查询结果组装成数组、逗号连接或者对象

跌入凡尘的张公子

已于 2023-11-03 08:59:35 修改

阅读量454

点赞数

文章标签： mysql 数据库

于 2023-10-13 10:28:05 首次发布

本文链接：https://blog.csdn.net/zh555888/article/details/133805252

版权

MYSQL将查询结果组装成数组、逗号连接或者对象

SELECT
  t1.TRUCK_PLAN_CODE,
	t1.DRIVER_CODE,
	t1.DRIVER_NAME,
	t1.DRIVER_PHONE,
	t1.IDENTITY_CODE,
	t1.CAR_NO,
	t1.TRUCK_LOAD_NUMBER,
	t1.LOGISTICS_CODE,
	t2.LOGISTICS_NAME,
	t1.PLAN_OUT_PLANT_TIME ,
	(
	select 
	CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('vin', VIN_NO)),
    ']'
	)
	from t_vl_bu_truck_plan_details  where TRUCK_PLAN_ID  = t1.TRUCK_PLAN_ID  
	)  as vinList,
p.TRANSPORT_STATUS ,
lookup1.LOOKUP_VALUE_NAME AS TRANSPORT_STATUS_NAME
FROM
	t_vl_bu_truck_plan  t1
	left join t_vl_db_logistics  t2 on t1.LOGISTICS_CODE=t2.LOGISTICS_CODE and  t2.IS_ENABLE='1'
	left join t_vl_bu_transport_plan p on p.TRUCK_PLAN_ID = t1.TRUCK_PLAN_ID 
LEFT JOIN t_vl_lookup_value lookup1 ON lookup1.LOOKUP_VALUE_CODE=p.TRANSPORT_STATUS AND lookup1.LOOKUP_TYPE_CODE="VL_TRAN_PLAN_STATUS"
WHERE
t1.IS_ENABLE='1'
	and t1.TRUCK_STATUS = '2'