有这样的一张表申请人 操作人
1 5
2 6另一张表
id 用户名
1 a
2 b
5 c
6 d想写一句sql语句,查出第一张表中对应id的用户名结果集
申请人 操作人
a c
b d
如何写比较有效率呢?谢谢各位老师 我用的mysqlmysqlsqljoin
1 5
2 6另一张表
id 用户名
1 a
2 b
5 c
6 d想写一句sql语句,查出第一张表中对应id的用户名结果集
申请人 操作人
a c
b d
如何写比较有效率呢?谢谢各位老师 我用的mysqlmysqlsqljoin
SELECT t.name proposer ,t1.name operator FROM one AS o
LEFT JOIN two AS t ON o.proposer=t.id
LEFT JOIN two as t1 ON t1.id=o.operator
详细参考:
-- 创建表1
CREATE TABLE one(
id int(7) NOT null auto_increment,
proposer smallint(5) not NULL,
operator SMALLINT(5) not null,
PRIMARY key(id)
)ENGINE=MYISAM DEFAULT CHARSET=UTF8;
-- 插入表1中数据
INSERT INTO one (proposer,operator)VALUES(1,5),(2,6)
SELECT proposer,operator FROM one
-- ---one-表1---------------
-- proposer operator
-- 1 5
-- 2 6
-- 创建表2-----------------
CREATE TABLE `two` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
-- 插入表2中数据
INSERT INTO two(id,name) VALUES(1,'a'),(2,'b'),(5,'c'),(6,'d')
SELECT id,name FROM two;
-- -two--表二---------------
-- id name
-- 1 a
-- 2 b
-- 5 c
-- 6 d
-- --------------
SELECT t.name proposer ,t1.name operator FROM one AS o
LEFT JOIN two AS t ON o.proposer=t.id
LEFT JOIN two as t1 ON t1.id=o.operator
-- --展示结果-----------------
-- proposer operator
--
-- a c
-- b d
-- -------------------
SELECT t.name proposer ,t1.name operator FROM one AS o ,two AS t ,two AS t1
WHERE o.proposer=t.id AND t1.id=o.operator
-- proposer operator
-- a c
-- b d
-- -------------------