MySQL 2级MENU查询 [英] MySQL 2 level MENU Query
本文介绍了MySQL 2级MENU查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试执行MySQL请求以获取2级菜单(父级和子级)...
I'm trying to do a MySQL request to retreive a 2 level menu (parent and children)...
当然只有1张桌子:
idCategory | Title | idCategoryParent | DisplayOrder
1 | cat1 | NULL | 1
2 | sub-cat1 | 1 | 1
3 | sub-cat2 | 1 | 2
4 | cat2 | NULL | 2
5 | sub-cat3 | 4 | 1
6 | sub-cat4 | 4 | 2
7 | cat3 | NULL | 3
我正在寻找那些结果:
titleCat | titleSubCat | idCategory
cat1 | sub-cat1 | 1
cat1 | sub-cat2 | 1
cat2 | sub-cat3 | 4
cat2 | sub-cat4 | 4
cat3 | NULL | 7
或者类似的东西也可以:
OR something like that would be fine too :
cat1 | null | 1
cat1 | sub-cat1 | 1
cat1 | sub-cat2 | 1
etc..
我尝试了类似的操作:
SELECT subcat.title as catTitle, cat.title as parentTitle, subcat.idCategory as catIdCategory, subcat.idCategoryParent
FROM `test_category` as cat
RIGHT OUTER JOIN test_category as subcat ON cat.idCategory=subcat.idCategoryParent
不错,但是我很难尝试订购记录...
Doesn't work bad but I struggle trying to order the records...
如果您想尝试一下,这里是SQL Dump:
Here's the SQL Dump if you want to try it :
--
-- Table structure for table `test_category`
--
CREATE TABLE IF NOT EXISTS `test_category` (
`idCategory` int(11) NOT NULL AUTO_INCREMENT,
`idCategoryParent` int(11) DEFAULT NULL,
`title` varchar(20) NOT NULL,
`order` int(11) NOT NULL,
PRIMARY KEY (`idCategory`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `test_category`
--
INSERT INTO `test_category` (`idCategory`, `idCategoryParent`, `title`, `order`) VALUES
(1, NULL, 'cat1', 1),
(2, 1, 'sub-cat1', 1),
(3, 1, 'sub-cat2', 2),
(4, NULL, 'cat2', 2),
(5, 4, 'sub-cat3', 1),
(6, 4, 'sub-cat4', 2),
(7, NULL, 'cat3', 3);
谢谢! :)
推荐答案
您的查询几乎是正确的,但是如果要使用没有子类别的类别,则需要使用LEFT JOIN
,并且应该从第一张桌子.
Your query is almost correct, but you need to use LEFT JOIN
if you want categories with no subcategories, and you should select only first-level categories from the first table.
SELECT t1.title, t2.title, t1.idCategory
FROM
test_category t1
LEFT JOIN test_category t2 ON t2.idCategoryParent=t1.idCategory
WHERE t1.idCategoryParent IS NULL
ORDER BY t1.DisplayOrder, t2.DisplayOrder
这篇关于MySQL 2级MENU查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文