表1 id, name
表2 inid, outid, time表2的inid, outid都对应表1的id字段, 现要两个表联合查询出类似:inname , outname, time
的结果, sql语句应该如何写呢我试验过程:create database test00;
use test00;
create table t1
(
id int(3),
name varchar(10)
);create table t2
(
inid int(3),
outid int(3),
time date
);insert into t1 values(1,’li’);
insert into t1 values(2,’wang’);
insert into t1 values(3,’bai’);
insert into t1 values(4,’liu’);
insert into t2 values(1,2,20100101);
insert into t2 values(2,3,20090808);
insert into t2 values(3,4,20300909);
insert into t2 values(4,3,20000202);
insert into t2 values(3,1,20010101);
表2 inid, outid, time表2的inid, outid都对应表1的id字段, 现要两个表联合查询出类似:inname , outname, time
的结果, sql语句应该如何写呢我试验过程:create database test00;
use test00;
create table t1
(
id int(3),
name varchar(10)
);create table t2
(
inid int(3),
outid int(3),
time date
);insert into t1 values(1,’li’);
insert into t1 values(2,’wang’);
insert into t1 values(3,’bai’);
insert into t1 values(4,’liu’);
insert into t2 values(1,2,20100101);
insert into t2 values(2,3,20090808);
insert into t2 values(3,4,20300909);
insert into t2 values(4,3,20000202);
insert into t2 values(3,1,20010101);
+------+------+
| id | name |
+------+------+
| 1 | li |
| 2 | wang |
| 3 | bai |
| 4 | liu |
+------+------+
4 rows in set (0.00 sec)mysql> select * from t2;
+------+-------+------------+
| inid | outid | time |
+------+-------+------------+
| 1 | 2 | 2010-01-01 |
| 2 | 3 | 2009-08-08 |
| 3 | 4 | 2030-09-09 |
| 4 | 3 | 2000-02-02 |
| 3 | 1 | 2001-01-01 |
+------+-------+------------+
5 rows in set (0.00 sec)mysql> select a.name,b.name,t2.time
-> from t2 ,t1 a,t1 b
-> where t2.inid=a.id
-> and t2.outid=b.id;
+------+------+------------+
| name | name | time |
+------+------+------------+
| li | wang | 2010-01-01 |
| wang | bai | 2009-08-08 |
| bai | liu | 2030-09-09 |
| liu | bai | 2000-02-02 |
| bai | li | 2001-01-01 |
+------+------+------------+
5 rows in set (0.00 sec)mysql>