求一SQL语句！

Question

表结构及数据是这样的！CREATE TABLE `members` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名',
  `passwd` varchar(40) DEFAULT NULL COMMENT '登录密码',
  `agent` int(11) DEFAULT NULL COMMENT '上级id',
  `alevel` int(11) DEFAULT NULL COMMENT '层次',
  PRIMARY KEY (`Id`));INSERT INTO `members` VALUES (1,'root','7409f549ba87a4b11ee741bb5124bd9e70a9cd83',0,1);
INSERT INTO `members` VALUES (2,'member1',NULL,1,2);
INSERT INTO `members` VALUES (3,'member2',NULL,1,2);
INSERT INTO `members` VALUES (4,'member',NULL,1,2);
INSERT INTO `members` VALUES (5,'cdll1rp2nu4c2b7n',NULL,3,3);
INSERT INTO `members` VALUES (6,'4brlhov0qlczyv2m',NULL,4,3);
INSERT INTO `members` VALUES (7,'15rvnvcrqk5bfoyt',NULL,2,3);
INSERT INTO `members` VALUES (8,'tlrlpqg1d6172uc3',NULL,4,3);
INSERT INTO `members` VALUES (9,'c381j0vp4p2w198d',NULL,2,3);
INSERT INTO `members` VALUES (10,'9nthlrcauhkpc5z7',NULL,4,3);
现在需要把直接下级总数不够3个人，并且alevel（层次）不超过4层
的id列出来。并显示出下级个数。结果：ID     count
2       2
3       1
5       0
6       0
7       0
8       0
9       0
之前发的一个帖子，说明和数据都不够详细，给大家带来不便，多有包涵。

Answer 1

select a.id,count(b.id)
from members a left join Members b on a.id=b.agent
where a.alevel<4
group by a.id
having count(b.id)<3mysql> select * from members;
+----+------------------+------------------------------------------+-------+--------+
| Id | username         | passwd                                   | agent | alevel |
+----+------------------+------------------------------------------+-------+--------+
|  1 | root             | 7409f549ba87a4b11ee741bb5124bd9e70a9cd83 |     0 |      1 |
|  2 | member1          | NULL                                     |     1 |      2 |
|  3 | member2          | NULL                                     |     1 |      2 |
|  4 | member           | NULL                                     |     1 |      2 |
|  5 | cdll1rp2nu4c2b7n | NULL                                     |     3 |      3 |
|  6 | 4brlhov0qlczyv2m | NULL                                     |     4 |      3 |
|  7 | 15rvnvcrqk5bfoyt | NULL                                     |     2 |      3 |
|  8 | tlrlpqg1d6172uc3 | NULL                                     |     4 |      3 |
|  9 | c381j0vp4p2w198d | NULL                                     |     2 |      3 |
| 10 | 9nthlrcauhkpc5z7 | NULL                                     |     4 |      3 |
+----+------------------+------------------------------------------+-------+--------+
10 rows in set (0.01 sec)mysql> select a.id,count(b.id)
    -> from members a left join members b on a.id=b.agent
    -> where a.alevel<4
    -> group by a.id
    -> having count(b.id)<3;
+----+-------------+
| id | count(b.id) |
+----+-------------+
|  2 |           2 |
|  3 |           1 |
|  5 |           0 |
|  6 |           0 |
|  7 |           0 |
|  8 |           0 |
|  9 |           0 |
| 10 |           0 |
+----+-------------+
8 rows in set (0.00 sec)mysql>

Answer 2

SELECT b.id,COUNT(a.id) FROM members a RIGHT JOIN members b ON a.agent=b.Id 
WHERE b.alevel<4
GROUP BY b.id
HAVING COUNT(a.id)<3

Answer 3

select a.id,count(b.id) from members a left join members b on a.id=b.agent where a.alevel<4 group by a.id
having count(*)<3