选择并列出孩子和父母 [英] SELECT and list children and parent
问题描述
我需要一个SQL查询来执行以下COMPLEX任务...
I need a SQL query for the following COMPLEX task...
我需要从名为parent_id
的列中进行选择.如果某行中的parent_id
为0,则表示它是一个类别(也有type
列,其中cat
表示该类别);如果某行中的parent_id
为1或更多,则表示它是一个类别.规则.规则的parent_id
是类别的rule_id
.
I need to to select from a column named parent_id
. If a row has 0 for parent_id
it means that it's a category (it also has the type
column which says cat
for category), if a row has 1 or more for parent_id
it means that it is a rule. The parent_id
of a rule, is the rule_id
of a category.
其中包含一些数据的表结构:
The table structure with some data in it:
我需要选择一种方式,即每个类别下都有其子类别.从图片中我们可以得到:
I need to select in a way, that EACH category, has its children under it. From the picture we would get this:
Sample Category 1
Sample rule 1
Sample Category 2
我尝试了一些从这里找到的查询,但是没有用.
I tried some some queries that I found from here, but none worked.
这是我上次尝试的时间:
This is what I tried last:
'SELECT *
FROM ' . RULES_TABLE . ' AS parent
LEFT JOIN ' . RULES_TABLE . ' AS child
ON child.parent_id = parent.rule_id
WHERE parent.parent_id = 0
AND parent.public = 1
ORDER BY parent.cat_position, child.rule_position';
仅返回示例规则1 .
想法?
推荐答案
试试这个小提琴 http://sqlfiddle.com/#!2/0a9c5/16 ,下面的sql代码
Try this fiddle http://sqlfiddle.com/#!2/0a9c5/16, sql code below
SELECT c.rule_id, c.rule_title, GROUP_CONCAT(r.rule_title)
FROM RULES_TABLE AS c
LEFT JOIN RULES_TABLE AS r
ON r.parent_id = c.rule_id
WHERE c.parent_id IS NULL AND c.public = 1
GROUP BY c.rule_id
ORDER BY c.cat_position, c.rule_position;
故意保留代码的php化,以保持语法高亮显示,但这似乎还是行不通的
left out php-ization of code on purpose to maintain syntax highlighting, which seems not to work anyway
如果超出了允许的最大group_concat大小,则可以增加它或使用以下版本,并在php中进行更多处理:
If you exceed maximum allowed group_concat size you can either increase it or use the following version, and do a bit more processing in php:
SELECT c.rule_id, c.rule_title, r.rule_title as r_rule_title
FROM RULES_TABLE AS c
LEFT JOIN RULES_TABLE AS r
ON r.parent_id = c.rule_id
WHERE c.parent_id IS NULL AND c.public = 1
ORDER BY c.cat_position, c.rule_position;
并且在php中,仅提供骨架代码,并使用实际值填写(假设您使用pdo且您的pdostatement var命名为$query
:
and in php, skeleton code provided only, fill in with the actual values, assuming you use pdo and your pdostatement var is named $query
:
$old_rule_id = null;
$rrt = array(); // will contain all r.rule_titles for a give c.rule_id
while( ($i = $query->fetch(PDO::FETCH_OBJ)) !== false ) {
if( $old_rule_id != $i->rule_id ) {
if( count($rrt) ) {
echo ... all the data for previous rule from $rrt ...
echo ... end of previous element ...
$rrt = array(); // empty it to collect new data
}
echo ... start of current element use data from $i->rule_id and $i->rule_title ...
$old_rule_id = $rule_id;
}
$rrt[] = $i->r_rule_title;
}
// the following is the same if from inside the while, minus reinitialization of $rrt;
if( count($rrt) ) {
echo ... all the data for previous rule from $rrt ...
echo ... end of previous element ...
}
用有效代码替换...之间的内容
replace stuff between ... with valid code
这篇关于选择并列出孩子和父母的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!