MySQL - 如何修改父/子选择查询以将更多子项添加到现有数组/JSON？
我有以下查询工作正常：
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
结果数组，使用一些 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父母（例如职业行为、医学伦理和法律等）。
BIG阳3回答
-
POPMUISE
当您尝试向原始查询添加额外部分时 - 这应该在“JOIN”部分之后完成，而不是在它之前完成。“JOIN”属于上一个查询。这个版本应该可以工作：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-- beginning of added query --UNION ALLSELECT strand.year AS name, strand.strand_name AS parentFROM strand WHERE strand.year is not NULL;我还添加了条件“WHERE strand.year is not NULL” - 如果您确定所有记录都设置了年份，请跳过这一部分。
随时随地看视频慕课网APP
PHP