select * from ecp_apply_plate_v; – v4.0.8时建立,可以正常查询
show create table ecp_apply_plate_v;
CREATE ALGORITHM=UNDEFINED DEFINER=zd_cgpt_test
@10.10.10.19
SQL SECURITY DEFINER VIEW ecp_apply_plate_v
(ID
, org_code
, use_status
, expiry_date
, pro_category
, pro_category_name
, q_level
, plate_type
, plate_name
) AS SELECT eoa
.ID
,eoa
.org_code
,1 AS use_status
,eoab
.board_expiry_date
AS expiry_date
,eoabc
.category_code
AS pro_category
,eoabc
.category_name
AS pro_category_name
,eoab
.board_q_level
AS q_level
,eoab
.board_type
AS plate_type
,eoab
.board_type_name
AS plate_name
FROM (zd_cgpt_test
.ecp_org_apply
AS eoa
LEFT JOIN zd_cgpt_test
.ecp_org_apply_board
AS eoab
ON eoab
.org_apply_id
=eoa
.id
) LEFT JOIN (SELECT eoabc
.board_id
,GROUP_CONCAT(eoabc
.board_pro_category_code
SEPARATOR ‘,’) AS category_code
,GROUP_CONCAT(eoabc
.board_pro_category_name
SEPARATOR ‘,’) AS category_name
FROM (zd_cgpt_test
.ECP_ORG_APPLY_BOARD_CATEGORY
AS eoabc
) GROUP BY eoabc
.board_id
) AS eoabc
ON eoabc
.BOARD_ID
=eoab
.board_id
select * from ecp_apply_plate_v_1; – v4.0.10时建立,查询时报错
show create table ecp_apply_plate_v_1;
CREATE ALGORITHM=UNDEFINED DEFINER=zd_cgpt_test
@10.10.10.17
SQL SECURITY DEFINER VIEW ecp_apply_plate_v_1
(id
, org_code
, use_status
, expiry_date
, pro_category
, pro_category_name
, q_level
, plate_type
, plate_name
) AS SELECT eoa
.id
,eoa
.org_code
,1 AS use_status
,eoab
.board_expiry_date
AS expiry_date
,eoabc
.category_code
AS pro_category
,eoabc
.category_name
AS pro_category_name
,eoab
.board_q_level
AS q_level
,eoab
.board_type
AS plate_type
,eoab
.board_type_name
AS plate_name
FROM (zd_cgpt_test
.ecp_org_apply
AS eoa
LEFT JOIN zd_cgpt_test
.ecp_org_apply_board
AS eoab
ON eoab
.org_apply_id
=eoa
.id
) LEFT JOIN (SELECT eoabc
.board_id
,GROUP_CONCAT(eoabc
.board_pro_category_code
SEPARATOR _UTF8MB4
‘,’) AS category_code
,GROUP_CONCAT(eoabc
.board_pro_category_name
SEPARATOR _UTF8MB4
‘,’) AS category_name
FROM (zd_cgpt_test
.ecp_org_apply_board_category
AS eoabc
) GROUP BY eoabc
.board_id
) AS eoabc
ON eoabc
.board_id
=eoab
.board_id