第一个表:
User_id User_name
1 小王
2 小李
3 张三第二个表:
sender received
1 2
3 1请大家看看,第二个表的字段分别为发送者和接收者,但我们的程序员设计数据库的时候,数字即是第一个表的User_id来的,
我现在要把User_id换成对应的User_name值,即下面一张表:
sender received
小王 小李
张三 小王不知道我的意思表达清楚没有。
请问应该怎么写SQL语句呢?谢谢!
User_id User_name
1 小王
2 小李
3 张三第二个表:
sender received
1 2
3 1请大家看看,第二个表的字段分别为发送者和接收者,但我们的程序员设计数据库的时候,数字即是第一个表的User_id来的,
我现在要把User_id换成对应的User_name值,即下面一张表:
sender received
小王 小李
张三 小王不知道我的意思表达清楚没有。
请问应该怎么写SQL语句呢?谢谢!
select t1.User_name,t2.User_name from 第一个表 t1,第一个表 t2,第二个表 t3
where t1.User_id=t3.sender and t1.User_id=t3.received;
mysql> create table A (id int,name varchar(10));
Query OK, 0 rows affected (0.06 sec)mysql> create table C (sender int,received int);
Query OK, 0 rows affected (0.06 sec)mysql> insert into A values(1,'xiaowang'),(2,'xiaoli'),(3,'zhangsan');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> insert into B values(1,2),(3,1);
ERROR 1146 (42S02): Table 'csdn.b' doesn't exist
mysql> insert into C values(1,2),(3,1);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from A
-> ;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaowang |
| 2 | xiaoli |
| 3 | zhangsan |
+------+----------+
3 rows in set (0.00 sec)mysql> select * from C;
+--------+----------+
| sender | received |
+--------+----------+
| 1 | 2 |
| 3 | 1 |
+--------+----------+
2 rows in set (0.00 sec)
mysql> select A.name sender,B.name received from A,A B,C where A.id=C.sender and
B.id=C.received;
+----------+----------+
| sender | received |
+----------+----------+
| zhangsan | xiaowang |
| xiaowang | xiaoli |
+----------+----------+
2 rows in set (0.00 sec)
from 第二个表 a inner join 第一个表 b1 on a.sender=b1.User_id
inner join 第一个表 b2 on a.received=b2.User_id