我只是想澄清我的疑问。我想知道如果我这样做是正确的，或者如果有一个更好的方法来优化我的查询。我有两个简单的表
Parents
------------------------
+ id + title +
------------------------
+ 1 + title parent 1 +
------------------------
+ 2 + title parent 2 +
------------------------
and so on...
Children
--------------------------------------------
+ id + parent_id + child_text +
--------------------------------------------
+ 1 + 1 + some test +
--------------------------------------------
+ 2 + 1 + more text... +
--------------------------------------------
+ 3 + 2 + other text +
--------------------------------------------
+ 4 + 2 + some more other text...+
--------------------------------------------
and so on... you get it...
我想要得到的结果是:
Title parent 1
some text
more text
Title parent 2
other text
some more other text
但代价是将一个查询循环到另一个查询，例如:
foreach(getParents() as $parent){ //getParents selects all the parents
getChildren($parent['id']); // selects all children where parent_id= $parent['id']
}
我想知道是否将查询放入循环的方式是不好的做法，如果有更好的方法来做到这一点，也许只有一个查询。
追加查询，然后使用联合语句…
where子句只是一个例子，表明它需要同时包含两个查询以确保选择正确的行。
SELECT title, id, 0 FROM parents
WHERE id < 10
UNION
SELECT child_text, parent_id, id FROM children
WHERE id < 10
ORDER BY 2, 3
转换成只有标题的报告do:
SELECT report.title
FROM
(SELECT title AS title, id AS parent_id, 0 AS child_id
FROM parents
UNION
SELECT child_text, parent_id, id FROM children
) report
ORDER BY report.parent_id, report.child_Id
或将where子句移到
之外SELECT report.title
FROM
(SELECT title AS title, id AS parent_id, 0 AS child_id
FROM parents
UNION
SELECT child_text, parent_id, id FROM children) report
WHERE report.parent_id < 3
ORDER BY report.parent_id, report.child_Id
http://www.sqlfiddle.com/!2/d63f4/8