一个 SQL 联表的弱智问题

[kmvan]

messages 表
ID---author---receiver---content
1------1---------2---------你好
2------2---------1---------草泥马

users 表
ID---------name
1----------小明
2----------小花

如何查询才能显示如下结果呢？

ID---content------author------receiver------author_name---receiver_name
1-----你好-----------1-------------2-------------小明------------小花


蛋疼，不怎么擅长sql= =

[catfan]

用 JOIN 语法啊

[kmvan]

我只能查到一个。。。如下，我只能查到作者。。。不能同时查到接收者..
SELECT msg.*, users.name FROM message msg, users
WHERE msg.ID = 1 and msg.author = users.ID

[yangqi]

SELECT
m.ID,
m.author,
m.receiver,
u1.author_name,
u2.author_name as receiver_name
FROM messages m
LEFT JOIN users u1 ON u1.ID=m.author
LEFT JOIN users u2 ON u2.ID=m.receiver

[kmvan]

@yangqi 你牛...正解

[Seans]

@yangqi 帮你调整下：

SELECT
m.ID,
m.content,
m.author,
m.receiver,
u1.name as author_name,
u2.name as receiver_name
FROM messages m
LEFT JOIN users u1 ON u1.ID = m.author
LEFT JOIN users u2 ON u2.ID = m.receiver;

不客气～

[ipconfiger]

考虑过效率吗，骚年

[kmvan]

@Seans 3Q

[Seans]

@ipconfiger 不知道这样写效率会不会高点？

select
n.id,
n.content,
n.author,
n.receiver,
n.name author_name,
u2.name receiver_name
from (select
m.id,
m.author,
m.receiver,
m.content,
u1.name
from messages m, user u1
where m.author = u1.id) n, user u2
where n.receiver = u2.id;

[yangqi]

@Seans 这样效率反而会低, 括号里面derived table没有索引的

[Seans]

@yangqi 我不是专业的dba，也不知道该怎样来比较两条sql的效率，是看执行时间吗？只是之前写left join的时候在大表查询的时确实很慢

[yangqi]

@Seans 最简单的explain下可以看出来, 然后可以用profile来比较.

你第二种写法还是Join, 只不过是inner join, 而且括号中的select会生成一个临时表, 没有索引), 所以效率反而会更低

[alore]

select *,
(select name from users where id=a.author) as author_name,
(select name from users where id=a.receiver) as receiver_name
from messages as a

效率问题,不用多想.等你上百万数据,上百万流量时再说.