我做了一个简单的表格来了解父母与子女之间的关系,但是我似乎没有得到正确的结果。我要排序
表格示例:
================================================== ==========
CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
--
-- Dumping data for table `test`
--
INSERT INTO `test` (`id`, `parent_id`, `name`) VALUES
(1, 4, 'Zorg'),
(2, 0, 'Woordolack'),
(3, 4, 'Akriller'),
(4, 0, 'Metabrusher'),
(5, 2, 'Intersplitter'),
(6, 0, 'Beaverbrain'),
(7, 4, 'Torgeoruos'),
(8, 2, 'Deptezaurus');
================================================== ==========
Beaverbrain
Metabrusher
-> Akriller
-> Torgeoruos
-> Zorg
Woordolack
-> Deptezaurus
-> Intersplitter
================================================== ==========
我应该说这只是一个示例表,目的只是为了说明这一点,因此可以通过任何方式对其进行修改以获得正确的结果。
您可以尝试此查询
SELECT IF(child = '',parent,CONCAT('->',child)) as value
FROM
(SELECT parent.name as parent,child.name as child
FROM test parent
INNER JOIN
(SELECT id,parent_id,name FROM test
UNION
SELECT null,id,'' FROM test
WHERE parent_id = 0)child
WHERE parent.parent_id = 0
AND child.parent_id = parent.id
ORDER BY parent,child
)parent_child
该查询执行的是带有测试的INNER JOIN测试,并找出哪些是父级和子级。内部UNIONSELECT null,id,'' FROM test WHERE parent_id = 0
为显示目的创建一个空子级。然后,外部SELECT会查看子代是否为'',它会打印出父代,否则会->
在子代前出现。这样您就可以得到想要的结果。
以下版本可能会更快一些。由于我们将使用NULL子代创建父代,而不是将子代用作INNER JOIN的一部分(如上所述)。
SELECT IF(child IS NULL,parent,CONCAT('->',child)) as value
FROM
(SELECT parent.name as parent,child.name as child
FROM test parent
INNER JOIN test child ON child.parent_id = parent.id
WHERE parent.parent_id = 0
UNION
SELECT test.name as parent,NULL as child #This creates a NULL child row
FROM test WHERE test.parent_id = 0 #for parent display purpose
ORDER BY parent,child
)parent_child
但是,我将改用下面的查询,并在PHP内部,检查isParent
标志是否为1或0,然后显示适当的字段。
SELECT 0 as isParent,parent.name as parent,child.name as child,
parent.id as parentId,child.id as childId
FROM test parent
INNER JOIN test child ON child.parent_id = parent.id
WHERE parent.parent_id = 0
UNION
SELECT 1 as isParent,test.name as parent,NULL as child,
test.id as parentId,NULL as childId
FROM test WHERE test.parent_id = 0
ORDER BY parent,child
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句