用的是mysql,请问下面SQL语句应该怎么写?
表结构如下,其中pid存放分类id,pid=0时表示为大类,不为0时为小类,存放所属大类的id
如下:
id name pid order
1 aaa 0 1
2 bbb 0 2
3 ccc 0 3
4 ddd 1 1
5 eee 1 2
6 fff 2 2
7 ggg 2 1
8 hhh 3 1现在要按下面排序输出,即某大类后面跟着所属小类,并且大类小类都按order由小到大排序id name pid order
1 aaa 0 1
4 ddd 1 1
5 eee 1 22 bbb 0 2
7 ggg 2 1
6 fff 2 23 ccc 0 3
8 hhh 3 1谢谢!
表结构如下,其中pid存放分类id,pid=0时表示为大类,不为0时为小类,存放所属大类的id
如下:
id name pid order
1 aaa 0 1
2 bbb 0 2
3 ccc 0 3
4 ddd 1 1
5 eee 1 2
6 fff 2 2
7 ggg 2 1
8 hhh 3 1现在要按下面排序输出,即某大类后面跟着所属小类,并且大类小类都按order由小到大排序id name pid order
1 aaa 0 1
4 ddd 1 1
5 eee 1 22 bbb 0 2
7 ggg 2 1
6 fff 2 23 ccc 0 3
8 hhh 3 1谢谢!
select * from (
SELECT * FROM TTp A where pid=0
union all
select pid,name,id,order from ttp where pid>0) a
order by id,if(pid=0,-1,order)
select *
from ttp a left join ttp b on a.pid=b.id
order by IFnull(b.id,a.id) , a.pid,a.order
from tab1 a left join tab1 b on a.pid=b.id
where a.pid=0
order by IFNULL(b.id,a.id),IFNULL(b.order,a.order),b.order
`nCId` double ,
`nPId` double ,
`sPath` varchar (3000),
`nOrder` double ,
`sName` varchar (300),
`sPatch` varchar (150),
`sRe` varchar (900)
);
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('1','0','/1/','1','游戏分类','/upload/categoryLogo/1203/120327093730.gif','游戏大类别');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('2','1','/1/2/','1','策略模拟','/upload/categoryLogo/1203/120327093719.gif','dgfsgsdfg');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('3','1','/1/3/','1','角色冒险','/upload/categoryLogo/1203/120327093713.gif','gergerg');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('4','1','/1/4/','1','动作射击','/upload/categoryLogo/1203/120327093706.gif','fsadfasdf');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('5','1','/1/5/','2','体育竞技','/upload/categoryLogo/1203/120327093659.gif','师德师风');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('6','1','/7/6/','1','棋牌休闲','/upload/categoryLogo/1203/120327093725.gif','下棋休闲');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('7','0','/7/','2','益智游戏','/upload/categoryLogo/1203/120327093648.gif','智力游戏');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('91','90',NULL,'3','小分类','/upload/categoryLogo/1203/120327093926.gif','小分类');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('90','0',NULL,'3','大类','/upload/categoryLogo/1203/120327093846.gif','阿斯顿发生');
insert into `tbCategory` (`nCId`, `nPId`, `sPath`, `nOrder`, `sName`, `sPatch`, `sRe`) values('92','7',NULL,'2','智力','/upload/categoryLogo/1203/120327093948.gif','阿斯发');
结果输出某大类后面跟着所属小类,并且大类小类都按nOrder由小到大排序nCId sName nPId nOrder
1 aaa 0 1
4 ddd 1 1
5 eee 1 22 bbb 0 2
7 ggg 2 1
6 fff 2 23 ccc 0 3
8 hhh 3 1
晕,楼上叫贴出建表及插入数据的sql,我在1楼只不过把表简化了而已
sql是短板,这个排序搞了很久没搞定输出结果:nPId存放分类id,也就是表中的nCId,nPId=0时表示为大类, 不为0时为小类,为小类时其中存放所属大类的的id(nCId),结果输出某大类后面跟着所属小类,并且大类小类都按nOrder由小到大排序,类似1楼..好吧,我承认我表达能力很差...
+------+------+------+-------+
| id | name | pid | order |
+------+------+------+-------+
| 1 | aaa | 0 | 1 |
| 2 | bbb | 0 | 2 |
| 3 | ccc | 0 | 3 |
| 4 | ddd | 1 | 1 |
| 5 | eee | 1 | 2 |
| 6 | fff | 2 | 2 |
| 7 | ggg | 2 | 1 |
| 8 | hhh | 3 | 1 |
+------+------+------+-------+
8 rows in set (0.00 sec)mysql>
mysql> select a.id,a.name,a.pid,a.order
-> from lord001643 a left join lord001643 b on a.pid=b.id
-> order by IFNULL(b.id,a.id),IFNULL(b.order,a.order),b.order;
+------+------+------+-------+
| id | name | pid | order |
+------+------+------+-------+
| 1 | aaa | 0 | 1 |
| 4 | ddd | 1 | 1 |
| 5 | eee | 1 | 2 |
| 2 | bbb | 0 | 2 |
| 7 | ggg | 2 | 1 |
| 6 | fff | 2 | 2 |
| 3 | ccc | 0 | 3 |
| 8 | hhh | 3 | 1 |
+------+------+------+-------+
8 rows in set (0.00 sec)mysql>