给大家拜年了！问个MYySQL的left join 问题

Question

写这么多字我知道各位看着也辛苦，但是我只想尽力清楚的表达我的意思，我很想知道这是为什么，希望大家帮帮忙，花点时间看看，同时祝大家新年大吉，事业有成！！
我有一个树形结果的表，内容大致如下：
name: c_r
+----+-----+-----+-------+
| id | Cid | Pid | exist |
+----+-----+-----+-------+
|  3 |   1 |   0 | 1     |
|  4 |   2 |   1 | 1     |
|  5 |   3 |   2 | 1     |
|  1 |   4 |   1 | 1     |
|  2 |   5 |   1 | 1     |
|  6 |   6 |   4 | 1     |
|  7 |   7 |   2 | 1     |
+----+-----+-----+-------+
我想查询某个父节点对应的子节点（子节点的子节点不用查出），但是我又要同时知道该父节点的子节点下面还有没有子节点（count(cid)）
例如：我要查询pid=A下面的所有cid（所有cid用B表示）同时查出B下面还有没有子节点；有个主意的地方是：当B下面没有子节点的时候，A和B也应该在result set里面只是count为0；
我想到的是用表的自身连接，并且是left join
我的SQL语句是：
select c1.pid,c1.cid, count(c2.cid) from c_r as c1 left join c_r as c2 on c1.cid = c2.pid and c1.pid=1 group by c1.cid
但是查询到的结果是：
+-----+-----+---------------+
| pid | cid | count(c2.cid) |
+-----+-----+---------------+
|   0 |   1 |             3 |
|   1 |   2 |             2 |
|   2 |   3 |             0 |
|   1 |   4 |             1 |
|   1 |   5 |             0 |
|   4 |   6 |             0 |
|   2 |   7 |             0 |
+-----+-----+---------------+
我在查询语句中已经写了c1.pid = 1 ，那结果不是应该只有pid=1的记录吗？怎么全部都出来了，而且我发现要是去掉c1.pid = 1,查询的结果还是一样，这令我很费解；
我试了另一条：select c1.pid,c1.cid, count(c2.cid) from c_r as c1 inner join c_r as c2 on c1.cid = c2.pid and c1.pid=1 group by c1.cid;
结果：
+-----+-----+---------------+
| pid | cid | count(c2.cid) |
+-----+-----+---------------+
|   1 |   2 |             2 |
|   1 |   4 |             1 |
+-----+-----+---------------+
虽然是只找出pid=1，但是少了一条记录，
+-----+-----+---------------+
| pid | cid | count(c2.cid) |
+-----+-----+---------------+
|   1 |   5 |             0 |
+-----+-----+---------------+
我知道这是由于inner join 的特性决定的，因为cid=5的节点下面已经没有子节点了，所以自身连接时c1.cid = c2.pid这个条件满足不了，于是这条记录被放弃了，但是left join怎么解决不了这个问题？
我想知道在这条查询语句中left join为什么达不到我预想的效果？是我的SQL语句的问题，还是我对left join理解的错误？
还有，有没有一条SQL语句可以解决这个问题?
我做后没有办法的办法是：
select tt.row1, tt.row2, tt.row3 from (select c1.pid as row1,c1.cid as row2, count(c2.cid) as row3 from c_r as c1 left join c_r as c2 on c1.cid = c2.pid group by c1.cid) as tt where tt.row1 = 1;
这才得出我要的结果+_+
+------+------+------+
| row1 | row2 | row3 |
+------+------+------+
|    1 |    2 |    2 |
|    1 |    4 |    1 |
|    1 |    5 |    0 |
+------+------+------+

Answer 1

为啥我觉得问题出在你的数据结构上？你这个结构，能支持多二叉以上的树么？id cid pid 各自代表的含义是：本节点ID、子节点ID、父节点ID？那么：
| 4 | 2 | 1 | 1 |
| 1 | 4 | 1 | 1 |
| 2 | 5 | 1 | 1 |意味着节点1、2、4，的父节点都是1，那身为父节点的1，它的子节点你存哪个？

Answer 2

不好意思，我没说明，那个id你就当做物理主键就行了，在查找时可以忽略，其实这个表完全可以简化为：
+---+----+
| Cid | Pid |
+----+-----+
| 1     | 0      |
| 2     | 1      |
| 3     | 2      | 
| 4     | 1      |
| 5     | 1      |
| 6     | 4      |
| 7     | 2      | 
+----+-----+

Answer 3

例如：我要查询pid=A下面的所有cid（所有cid用B表示）同时查出B下面还有没有子节点；有个主意的地方是：当B下面没有子节点的时候，A和B也应该在result set里面只是count为0；这个要求，用表连接操作并不算复杂，但似乎不是你写的这种样子。你用了外连接，就意味着无论能不能连接上，左表一定会完整的显示出来，所以当然“怎么全部都出来了”。建议碰到这种复杂查询，先分步完成，然后再尝试进行二次优化，而不要马上就打算一气呵成，比如：
1、查询pid=A下面的所有cid（用B表示）；
——Select cid From c_r Where pid = 1;2、查出B下面还有没有子节点（注意那么B在这里就已经相对成为父节点了）；
——
Select pid, count(cid) 
From c_r
Where pid in (??,??,??)
Order By pid
那么接下来要怎么组合起来，显然就是理所当然的用嵌套子查询来做啦：
Select pid, count(cid) 
From c_r
Where pid in (Select cid From c_r Where pid = 1)
Order By pid
最后我表示我没有环境测试这个SQL，烦请楼主测试。

Answer 4

我也是菜鸟，本来这个问题没那么复杂，应该是我对left join的理解的错误或者是对其内部运行原理不懂导致的。估计是我这么多的文字吓到你了，呵呵。

Answer 5

感谢ldh911这么耐心的帮我解答！
       我用left join 是会把左表都显示出来，但是我on后面还有一个约束条件就是（左表）c1.pid=1,那最后结果不是只显示pid=1的结果吗？但我那条SQL却把左边的pid查询都出来了这里是不是就是我对left join理解上的错误？
       另外你那条SQL语句还是不能解决一个问题，
首先where里面的子查询确实把pid=1的记录查了出来，结果是：(2、4、5)
但是第二个查询条件cid的pid in (2、4、5)，结果只有（2、4）的cid 被查出来了，却缺了（5）
这就是我说的“注意”那里，（5）这个节点已经没有子节点了，所以查询（5）的子节点当然没有结果。
但是我需要的是即使（5）没有子节点，但是仍然显示出来（因为我查的是pid=1的cid），只是count那里为0。
       是否还有其他的解决办法呢？

Answer 6

在吃饭中，简单说下：你的错误在于，and条件不应该放在from子句中（因为from子句你用的是左外联结，也就是即便任何条件不满足，左边都一定全部出来，你要再次深刻理解这句话），而是必须重新写在where子句中：Select c1.pid,c1.cid, count(c2.cid) 
From c_r as c1 left join c_r as c2 on c1.cid = c2.pid 
Where c1.pid=1 
Group By c1.cid不过还么认真研究这样是否就能满足你的要求。至于其它解决方案，吃饱我再想想看。

Answer 7

非常感谢，这条SQL语句就是我要的！！再次感谢你的耐心解答！！
看来是我理解上存在误区，唉，基础不扎实。