MySQL连接序列

我正在try 在查询中使用CONCAT顺序语句从相关表构建分层数据.

以下查询起作用:

SELECT 
CONCAT('program:', p.program_pk) AS global_id,
p.program_name AS name,
NULL AS parent_global_id,
NULL AS column_to_sort_by 
FROM
program p

UNION ALL

-- institution
SELECT
CONCAT('institution:', i.institution_pk) AS global_id,
i.institution_name,
CONCAT('program:', i.program_fk) AS parent_global_id,
NULL AS column_to_sort_by 
FROM
institution i 

UNION ALL

-- institution-year
SELECT
CONCAT('institution:', y.institution_fk, ',year:', y.year_pk) AS global_id,
y.year_name,
CONCAT('institution:', y.institution_fk) AS parent_global_id,       
NULL AS column_to_sort_by  
FROM
year y

UNION ALL

-- institution-year-unit
SELECT 
CONCAT('institution:', u.institution_fk, ',year:', u.year_fk, ',unit:', u.unit_code) AS global_id,
u.unit_code,
CONCAT('institution:', u.institution_fk, ',year:', u.year_fk) AS parent_global_id,
NULL AS column_to_sort_by
FROM
unit u

现在我想添加另一个级别，但对格式还不够熟悉.类似于以下内容，但最后一组CONCAT的格式不正确...

SELECT 
CONCAT('program:', p.program_pk) AS global_id,
p.program_name AS name,
NULL AS parent_global_id,
NULL AS column_to_sort_by 
FROM
program p

UNION ALL

-- institution
SELECT
CONCAT('institution:', i.institution_pk) AS global_id,
i.institution_name,
CONCAT('program:', i.program_fk) AS parent_global_id,
NULL AS column_to_sort_by 
FROM
institution i 

UNION ALL

-- institution-year
SELECT
CONCAT('institution:', y.institution_fk, ',year:', y.year_pk) AS global_id,
y.year_name,
CONCAT('institution:', y.institution_fk) AS parent_global_id,       
NULL AS column_to_sort_by  
FROM
year y

UNION ALL

-- institution-year-unit
SELECT 
CONCAT('institution:', u.institution_fk, ',year:', u.year_fk, ',unit:', u.unit_code) AS global_id,
u.unit_code,
CONCAT('institution:', u.institution_fk, ',year:', u.year_fk) AS parent_global_id,
NULL AS column_to_sort_by
FROM
unit u

UNION ALL

    -- institution-year-unit-unit_group
SELECT 
CONCAT('institution:', u.institution_fk, ',year:', u.year_fk, 'unit:', u2.unit_code, ',unit_group:', ug.unit_fk) AS global_id,
ug.unit_group,
CONCAT('institution:', u.institution_fk, ',year:', u.year_fk, 'unit:', u2.unit_code) AS parent_global_id,   
NULL AS column_to_sort_by
FROM 
unit_group ug

架构:

CREATE TABLE `program` (
 `program_pk` int(1) NOT NULL AUTO_INCREMENT,
 `program_name` varchar(30) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified_by` varchar(20) NOT NULL,
 PRIMARY KEY (`program_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `institution` (
 `institution_pk` int(2) NOT NULL AUTO_INCREMENT,
 `institution_name` varchar(20) NOT NULL,
 `program_fk` int(1) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified_by` varchar(20) NOT NULL,
 PRIMARY KEY (`institution_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

CREATE TABLE `year` (
 `year_pk` int(1) NOT NULL AUTO_INCREMENT,
 `year_name` varchar(20) NOT NULL,
 `institution_fk` int(1) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified_by` varchar(20) NOT NULL,
 PRIMARY KEY (`year_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

    
CREATE TABLE `unit` (
 `unit_pk` int(2) NOT NULL AUTO_INCREMENT,
 `unit_code` varchar(8) NOT NULL,
 `unit_name` varchar(100) NOT NULL,
 `points` int(2) NOT NULL,
 `year_fk` int(1) NOT NULL,
 `institution_fk` int(1) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified_by` varchar(20) NOT NULL,
 PRIMARY KEY (`unit_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=78 DEFAULT CHARSET=latin1


CREATE TABLE `unit_group` (
 `unit_group_pk` int(11) NOT NULL AUTO_INCREMENT,
 `unit_group` varchar(40) NOT NULL,
 `type` varchar(15) NOT NULL,
 `unit_fk` int(2) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `modified_by` varchar(10) NOT NULL,
 PRIMARY KEY (`unit_group_pk`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=latin1


INSERT INTO `unit` (`unit_pk`, `unit_code`, `unit_name`, `points`, `year_fk`, `institution_fk`, `modified`, `modified_by`) VALUES
(1, 'OPTM4101', 'Principles', 6, 5, 1, '', ''),
(2, 'OPTM4102', 'Structure ', 6, 5, 1, '', '');


INSERT INTO `unit_group` (`unit_group_pk`, `unit_group`, `type`, `unit_fk`, `modified`, `modified_by`) VALUES
(1, 'Learning Events - OPTM4101', 'learning_event', 1, '2021-07-26 05:32:25', ''),
(2, 'Learning Events - OPTM4102', 'learning_event', 2, '2021-07-26 05:32:35', ''));