关力两次left join后的笛卡尔乘积的问题

[kimsung]

tablea1行数据
tableb2行数据
tablec6行数据

select * from tablea a left join tableb b on a.id = b.id left join tablec c on a.id = c.id

查询结果12行数据是正常吗???
怎么写才能显示6行数据

[kimsung]

create table tba \(id int, name varchar\);
create table tbb \(id int, name varchar\);
create table tbc \(id int, name varchar\);

insert into tba values\(1,'a'\);
insert into tbb values\(1,'b'\);
insert into tbb values\(1,'b'\);
insert into tbc values\(1,'c'\);
insert into tbc values\(1,'c'\);
insert into tbc values\(1,'c'\);
insert into tbc values\(1,'c'\);
insert into tbc values\(1,'c'\);
insert into tbc values\(1,'c'\);

tba.name   tbb.name  tbc.name
--------------------------------
a          b         c
null       b         c
null       null      c
null       null      c
null       null      c
null       null      c

[kimsung]

引用 3 楼 WWWWA 的回复:

举例说明要求 贴建表及插入记录的SQL，及要求结果出来看看

写上了

[kimsung]

引用 2 楼 rucypli 的回复:

12行刚好是1*2*6 要想6行 就把某些列is null的行去掉

写上了.

[ACMAIN_CHM]

mysql> select * from tba;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tbb;
+------+------+
| id   | name |
+------+------+
|    1 | b    |
|    1 | b    |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tbc;
+------+------+
| id   | name |
+------+------+
|    1 | c    |
|    1 | c    |
|    1 | c    |
|    1 | c    |
|    1 | c    |
|    1 | c    |
+------+------+
6 rows in set (0.00 sec)

mysql> set @a=0;set @b=0;set @c=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select a.name,b.name,c.name
    -> from (select @c:=@c+1 as i,name from tbc) c left join (select @b:=@b+1 as
 i,name from tbb) b on c.i=b.i
    ->  left join (select @a:=@a+1 as i,name from tba) a on c.i=a.i;
+------+------+------+
| name | name | name |
+------+------+------+
| a    | b    | c    |
| NULL | b    | c    |
| NULL | NULL | c    |
| NULL | NULL | c    |
| NULL | NULL | c    |
| NULL | NULL | c    |
+------+------+------+
6 rows in set (0.00 sec)

mysql>

[WWWWA]

举例说明要求
贴建表及插入记录的SQL，及要求结果出来看看

[rucypli]

12行刚好是1*2*6

要想6行 就把某些列is null的行去掉

[WWWWA]

举例说明要求
贴建表及插入记录的SQL，及要求结果出来看看

[kimsung]

引用 7 楼 ACMAIN_CHM 的回复:

mysql> select * from tba;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tbb;
+------+------+
| id   | name |
+------+------+
|    1 | b    |
|    1 | b    |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from tbc;
+------+------+
| id   | name |
+------+------+
|    1 | c    |
|    1 | c    |
|    1 | c    |
|    1 | c    |
|    1 | c    |
|    1 | c    |
+------+------+
6 rows in set (0.00 sec)

mysql> set @a=0;set @b=0;set @c=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select a.name,b.name,c.name
    -> from (select @c:=@c+1 as i,name from tbc) c left join (select @b:=@b+1 as
 i,name from tbb) b on c.i=b.i
    ->  left join (select @a:=@a+1 as i,name from tba) a on c.i=a.i;
+------+------+------+
| name | name | name |
+------+------+------+
| a    | b    | c    |
| NULL | b    | c    |
| NULL | NULL | c    |
| NULL | NULL | c    |
| NULL | NULL | c    |
| NULL | NULL | c    |
+------+------+------+
6 rows in set (0.00 sec)

mysql>

谢谢. 只能用rownum方式了... 而且这种sql在程序里面不好调用... 或者还有其他方法吗?

[kimsung]

谢谢ACMAIN 我的提问大部分都是ACMAIN解答的... 我这弱爆了. 唉!