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
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>
贴建表及插入记录的SQL,及要求结果出来看看
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
+------+------+
| 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>
而且这种sql在程序里面不好调用...
或者还有其他方法吗?
我的提问大部分都是ACMAIN解答的...
我这弱爆了. 唉!