MySQL -内部连接-全局顺序BY

Question

如何在以下全局排序查询的末尾实现ORDER BY子句?

在单个SELECT语句中不使用ORDER BY…例如，我想要ORDER BY le.learning_event_name。

我尝试在查询结束时添加le.learning_event_name AS le_name,,ORDER BY le_name ASC，但我在'order子句中得到未知列'le_name'。

SELECT CONCAT('program:', p.program_pk) AS global_id,
p.program_name AS name,
NULL AS parent_global_id
FROM program p
UNION ALL
SELECT CONCAT('program_group:', pg.program_group_pk) AS global_id,
pg.program_group,
CONCAT('program:', pg.program_fk) AS parent_global_id
FROM program_group pg
UNION ALL
SELECT CONCAT('program_group:', pog.program_group_fk, 'program_outcome_group:', pog.program_outcome_group) AS global_id,
pog.program_outcome_group,
CONCAT('program_group:', pog.program_group_fk) AS parent_global_id
FROM program_outcome_group pog
UNION ALL
SELECT 
CONCAT('program_group:', pog2.program_group_fk, 'program_outcome_group:', pog2.program_outcome_group, ',program_outcome:', po.program_outcome) AS global_id,
po.program_outcome,
CONCAT('program_group:', pog2.program_group_fk, 'program_outcome_group:', pog2.program_outcome_group) AS parent_global_id
FROM program_outcome po
INNER JOIN program_outcome_group pog2 ON po.program_outcome_group_fk = pog2.program_outcome_group_pk
UNION ALL
SELECT 
CONCAT('program_group:', pog3.program_group_fk, 'program_outcome_group:', pog3.program_outcome_group, ',program_outcome:', po2.program_outcome, ',unit:', u.unit_full_name) AS global_id,
u.unit_full_name,
CONCAT('program_group:', pog3.program_group_fk, 'program_outcome_group:', pog3.program_outcome_group, ',program_outcome:', po2.program_outcome) AS parent_global_id
FROM unit u
INNER JOIN program_outcome_unit_lookup uup ON u.unit_pk = uup.unit_fk
INNER JOIN program_outcome po2 ON po2.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog3 ON po2.program_outcome_group_fk = pog3.program_outcome_group_pk
UNION ALL
SELECT 
CONCAT('program_group:', pog3.program_group_fk, 'program_outcome_group:', pog3.program_outcome_group, ',program_outcome:', po2.program_outcome, ',unit:', u2.unit_full_name, ',unit_group:', ug.unit_group) AS global_id,
ug.unit_group,
CONCAT('program_group:', pog3.program_group_fk, 'program_outcome_group:', pog3.program_outcome_group, ',program_outcome:', po2.program_outcome, ',unit:', u2.unit_full_name) AS parent_global_id
FROM unit_group ug
INNER JOIN unit u2 ON u2.unit_pk = ug.unit_fk
INNER JOIN program_outcome_unit_lookup uup ON u2.unit_pk = uup.unit_fk
INNER JOIN program_outcome po2 ON po2.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog3 ON po2.program_outcome_group_fk = pog3.program_outcome_group_pk
UNION ALL
SELECT 
CONCAT('program_group:', pog4.program_group_fk, 'program_outcome_group:', pog4.program_outcome_group, ',program_outcome:', po3.program_outcome, ',unit:', u3.unit_full_name, ',unit_group:', ug2.unit_group,  ',learning_event:', le.learning_event_name) AS global_id,
le.learning_event_name,
CONCAT('program_group:', pog4.program_group_fk, 'program_outcome_group:', pog4.program_outcome_group, ',program_outcome:', po3.program_outcome, ',unit:', u3.unit_full_name, ',unit_group:', ug2.unit_group) AS parent_global_id
FROM learning_event le
INNER JOIN unit_group ug2 ON ug2.unit_group_pk = le.unit_group_fk
INNER JOIN unit u3 ON ug2.unit_fk = u3.unit_pk
INNER JOIN program_outcome_unit_lookup uup ON u3.unit_pk = uup.unit_fk
INNER JOIN program_outcome po3 ON po3.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog4 ON po3.program_outcome_group_fk = pog4.program_outcome_group_pk
JOIN learning_event_program_outcome_lookup lepol 
ON lepol.learning_event_fk = le.learning_event_pk 
AND lepol.program_outcome_fk = po3.program_outcome_pk
UNION ALL
SELECT 
CONCAT('program_group:', pog4.program_group_fk, 'program_outcome_group:', pog4.program_outcome_group, ',program_outcome:', po3.program_outcome, ',unit:', u3.unit_full_name, ',unit_group:', ug2.unit_group,  ',assessment:', t1.assessment) AS global_id,
t1.assessment,
CONCAT('program_group:', pog4.program_group_fk, 'program_outcome_group:', pog4.program_outcome_group, ',program_outcome:', po3.program_outcome, ',unit:', u3.unit_full_name, ',unit_group:', ug2.unit_group) AS parent_global_id
FROM assessment t1
INNER JOIN unit_group ug2 ON ug2.unit_group_pk = t1.unit_group_fk
INNER JOIN unit u3 ON ug2.unit_fk = u3.unit_pk
INNER JOIN program_outcome_unit_lookup uup ON u3.unit_pk = uup.unit_fk
INNER JOIN program_outcome po3 ON po3.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog4 ON po3.program_outcome_group_fk = pog4.program_outcome_group_pk
JOIN assessment_program_outcome_lookup t5 
ON t5.assessment_fk = t1.assessment_pk 
AND t5.program_outcome_fk = po3.program_outcome_pk

看到小提琴

Answer 1

我将在联合中添加一个或多个列，以保存要排序的值。像这样:

SELECT CONCAT('program:', p.program_pk) AS global_id,
p.program_name AS name,
NULL AS parent_global_id,
-- add the column(s) to sort on ; first SELECT is the template with as result that all other SELECTs should also have (in this case) 4 columns
NULL AS column_to_sort_by 
FROM program p
UNION ALL
SELECT CONCAT('program_outcome_group:', pog.program_outcome_group_pk) AS global_id,
pog.program_outcome_group,
CONCAT('program:', pog.program_fk) AS parent_global_id,

-- add the column to sort on ; I use same alias for recognisability
NULL AS column_to_sort_by 
FROM program_outcome_group pog
UNION ALL
SELECT 
CONCAT('program_outcome_group:', po.program_outcome_group_fk, ',program_outcome:', po.program_outcome) AS global_id,
po.program_outcome,
CONCAT('program_outcome_group:', po.program_outcome_group_fk) AS parent_global_id,

-- add the column to sort on ; I use same alias for recognisability
NULL AS column_to_sort_by 
FROM program_outcome po
UNION ALL
SELECT 
CONCAT('program_outcome_group:', pro.program_outcome_group_fk, ',program_outcome:', pro.program_outcome, ',unit:', u.unit_name) AS global_id,
u.unit_name,
CONCAT('program_outcome_group:', pro.program_outcome_group_fk, ',program_outcome:', pro.program_outcome) AS parent_global_id,

-- add the column to sort on ; I use same alias for recognisability
NULL AS column_to_sort_by 
FROM unit u
INNER JOIN program_outcome_unit_lookup uup ON u.unit_pk = uup.unit_fk
INNER JOIN program_outcome pro ON pro.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog2 ON pro.program_outcome_group_fk = pog2.program_outcome_group_pk
UNION ALL
SELECT 
CONCAT('program_outcome_group:', pro2.program_outcome_group_fk, ',program_outcome:', pro2.program_outcome, ',unit:', u2.unit_name, ',learning_event:', le.learning_event_name) AS global_id,
le.learning_event_name,
CONCAT('program_outcome_group:', pro2.program_outcome_group_fk, ',program_outcome:', pro2.program_outcome, ',unit:', u2.unit_name) AS parent_global_id,

-- add the column to sort on, which in this case is based on table-column ; I use same alias for recognisability
le.learning_event_name AS column_to_sort_by 
FROM learning_event le
INNER JOIN unit u2 ON le.unit_fk = u2.unit_pk
INNER JOIN program_outcome_unit_lookup uup ON u2.unit_pk = uup.unit_fk
INNER JOIN program_outcome pro2 ON pro2.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog2 ON pro2.program_outcome_group_fk = pog2.program_outcome_group_pk
JOIN learning_event_program_outcome_lookup lepol 
ON lepol.learning_event_fk = le.learning_event_pk 
AND lepol.program_outcome_fk = pro2.program_outcome_pk 
union all
SELECT 
CONCAT('program_outcome_group:', pro2.program_outcome_group_fk, ',program_outcome:', pro2.program_outcome, ',unit:', u2.unit_name, ',assessment:', t1.assessment) AS global_id,
t1.assessment,
CONCAT('program_outcome_group:', pro2.program_outcome_group_fk, ',program_outcome:', pro2.program_outcome, ',unit:', u2.unit_name) AS parent_global_id,

-- add the column to sort on ; I use same alias for recognisability
NULL AS column_to_sort_by 
FROM assessment t1
INNER JOIN unit u2 ON t1.unit_fk = u2.unit_pk
INNER JOIN program_outcome_unit_lookup uup ON u2.unit_pk = uup.unit_fk
INNER JOIN program_outcome pro2 ON pro2.program_outcome_pk = uup.program_outcome_fk
INNER JOIN program_outcome_group pog2 ON pro2.program_outcome_group_fk = pog2.program_outcome_group_pk
JOIN assessment_program_outcome_lookup t5 
ON t5.assessment_fk = t1.assessment_pk 
AND t5.program_outcome_fk = pro2.program_outcome_pk
ORDER BY
column_to_sort_by ASC

我会在UNION的每个SELECT中使用相同的列名(必要时使用别名)，这样您/其他人就可以读取到您试图"映射"的目标列。源列。

编辑:别名不会自动映射。在UNION的每个SELECT中列的顺序仍然很重要。但是使用相同的列名会增加可读性。