MySQL - 如何修改父/子选择查询以将更多子项添加到现有数组/JSON？

Question

我有以下查询工作正常：SELECT core_condition AS name, NULL AS parentFROM condition_theme_lookupUNION ALLSELECT theme_name AS name, condition_theme_lookup.core_condition AS parentFROM theme, condition_theme_lookupUNION ALLSELECT strand.strand_name AS name, theme.theme_name AS parentFROM strandJOIN theme ON theme.theme_pk = strand.theme_fk结果数组，使用一些 PHP，生成以下 JSON，到目前为止还不错，显示了“主题”父母的“链”子代：{    "name": "Condition",    "children": [{        "name": "Professional",        "children": [{            "name": "Professional Behavours"        }, {            "name": "Self-Care and Self-Awareness"        }, {            "name": "Medical Ethics and Law"        }]    }, {        "name": "Leader",        "children": [{            "name": "Teamwork and Leadership"        }, {            "name": "Collaborative Practice"        }, {            "name": "Health Systems and Careers"        }]    }, {        "name": "Advocate",        "children": [{            "name": "Health Advocacy"        }, {            "name": "Aboriginal Health"        }, {            "name": "Diversity and Inequality"        }, {            "name": "Health Promotion"        }]    }, {        "name": "Clinician",        "children": [{            "name": "Scientific Knowledge"        }, {            "name": "Patient Assessment and Clinical Reasoning"        }, {            "name": "Patient Management"        }, {            "name": "Patient Perspective"        }, {            "name": "Clinical Communication"        }, {            "name": "Quality Care"        }]    }, {        "name": "Educator",        "children": [{            "name": "Life-Long Learning"        }, {            "name": "Mentoring Relationships"        }, {            "name": "Patient Education"        }, {            "name": "Teaching and Learning"        }, {            "name": "Assessment and Evaluation"        }]    },我现在想添加相同的孩子集：'Year 1'、'Year 2'、'Year 3' 和'Year 4'，从 tablestrand.year到每个strand.strand_name父母（例如职业行为、医学伦理和法律等）。

Answer 1

当您尝试向原始查询添加额外部分时 - 这应该在“JOIN”部分之后完成，而不是在它之前完成。“JOIN”属于上一个查询。这个版本应该可以工作：

SELECT core_condition AS name, NULL AS parent

FROM condition_theme_lookup

UNION ALL

SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent

FROM theme, condition_theme_lookup

UNION ALL

SELECT strand.strand_name AS name, theme.theme_name AS parent

FROM strand

JOIN theme ON theme.theme_pk = strand.theme_fk

-- beginning of added query --

UNION ALL

SELECT strand.year AS name, strand.strand_name AS parent

FROM strand WHERE strand.year is not NULL;

我还添加了条件“WHERE strand.year is not NULL” - 如果您确定所有记录都设置了年份，请跳过这一部分。