用一条SQL语句查询顶级文件夹及其下级文件夹数目和下级文件数目

用一条SQL语句查询顶级文件夹及其下级文件夹数目和下级文件数目。

文件夹表FOLDER，SUPID字段是上级文件夹ID，为0表示是顶级文件夹，如下：

-- ----------------------------
-- Table structure for FOLDER
-- ----------------------------
CREATE TABLE `FOLDER` (
  `FOLDERID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) DEFAULT NULL,
  `SUPID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`FOLDERID`)
) ;

-- ----------------------------
-- Records of FOLDER
-- ----------------------------
INSERT INTO `FOLDER` VALUES ('1', 'a', '0');
INSERT INTO `FOLDER` VALUES ('2', 'b', '0');
INSERT INTO `FOLDER` VALUES ('3', 'c', '1');
INSERT INTO `FOLDER` VALUES ('4', 'd', '1');
INSERT INTO `FOLDER` VALUES ('5', 'e', '2');

mysql> select * from FOLDER;
+----------+------+-------+
| FOLDERID | NAME | SUPID |
+----------+------+-------+
|        1 | a    |     0 |
|        2 | b    |     0 |
|        3 | c    |     1 |
|        4 | d    |     1 |
|        5 | e    |     2 |
+----------+------+-------+
5 rows in set


文件表FILE，FOLDERID字段是所属文件夹ID，如下：

-- ----------------------------
-- Table structure for FILE
-- ----------------------------
CREATE TABLE `FILE` (
  `FILEID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) DEFAULT NULL,
  `FOLDERID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`FILEID`)
) ;

-- ----------------------------
-- Records of FILE
-- ----------------------------
INSERT INTO `FILE` VALUES ('1', 'f1', '1');
INSERT INTO `FILE` VALUES ('2', 'f2', '2');
INSERT INTO `FILE` VALUES ('3', 'f3', '1');

mysql> select * from FILE;
+--------+------+----------+
| FILEID | NAME | FOLDERID |
+--------+------+----------+
|      1 | f1   |        1 |
|      2 | f2   |        2 |
|      3 | f3   |        1 |
+--------+------+----------+
3 rows in set

SQL语句写法：

select FOLDERID, NAME as FOLDERNAME, (select COUNT(1) from FOLDER b where b.SUPID = a.FOLDERID) as SUBFLDRNUM, (select COUNT(1) from FILE c where c.FOLDERID = a.FOLDERID) as SUBFILENUM from FOLDER a where FOLDERID in (select FOLDERID from FOLDER where SUPID = 0);

执行语句：

mysql> select FOLDERID, NAME as FOLDERNAME, (select COUNT(1) from FOLDER b where b.SUPID = a.FOLDERID) as SUBFLDRNUM, (select COUNT(1) from FILE c where c.FOLDERID = a.FOLDERID) as SUBFILENUM from FOLDER a where FOLDERID in (select FOLDERID from FOLDER where SUPID = 0);
+----------+------------+------------+------------+
| FOLDERID | FOLDERNAME | SUBFLDRNUM | SUBFILENUM |
+----------+------------+------------+------------+
|        1 | a          |          2 |          2 |
|        2 | b          |          1 |          1 |
+----------+------------+------------+------------+
2 rows in set

查询结果正确！