日期:2014-05-20  浏览次数:20693 次

给大家拜年了!问个MYySQL的left join 问题
写这么多字我知道各位看着也辛苦,但是我只想尽力清楚的表达我的意思,我很想知道这是为什么,希望大家帮帮忙,花点时间看看,同时祝大家新年大吉,事业有成!!
我有一个树形结果的表,内容大致如下:
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 |
+------+------+------+


------解决方案--------------------
例如:我要查询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,烦请楼主测试。