sql server怎样一次从一个字段中查出两个值
例如有表A： 表B：
id name headpic id jid uid sid time
1 张三 1.jpg 1 23 2 3 2012-09-10 16:24:39.377
2 李四 2.jpg 2 43 1 2 2012-09-11 16:24:39.377
3 王五 3.jpg 3 24 3 2 2012-09-12 16:24:39.377
其中表A的headpic是人物头像图片，表B是一个日志评论表，jid是被评论的日志编号，uid是日志所有者的id，sid是评论日志的人的id，time是评论发布的时间。
我想要查询出最新两条评论相关的日志标号jid，评论日志的人的name 和headpic，以及日志所有者的name和time，查询结果大概如下：
jid uname(日志所有者的name) sname(发表日志的人的name) headpic(发表日志的人的头像) time
sql 语句该如何写啊？大哥些，帮帮忙啊。
第1个回答 2012-10-18
SELECT TOP 2 [B].[jid], [A1].[name], [A1].[headpic], [A2].[name], [A2].[headpic], [B].[time]
FROM [B]
INNER JOIN [A] AS [A1] ON [B].[uid] = [A1].[id]
INNER JOIN [A] AS [A2] ON [B].[sid] = [A2].[id]
ORDER BY [B].[time] DESC本回答被提问者采纳
FROM [B]
INNER JOIN [A] AS [A1] ON [B].[uid] = [A1].[id]
INNER JOIN [A] AS [A2] ON [B].[sid] = [A2].[id]
ORDER BY [B].[time] DESC本回答被提问者采纳
第2个回答 2012-10-18
select a.jid,u.uname,s.uname,s.headpic,a.[time] from
(select top 2 * from B order by [time] desc) as a,A as u,A as s
where a.uid=u.id and a.sid=s.id
(select top 2 * from B order by [time] desc) as a,A as u,A as s
where a.uid=u.id and a.sid=s.id
第3个回答 2012-10-18
select a.jid,b.name,b.headpic,c.name,c.headpic from B as b inner join A as a on a.id=b.uid
inner join A as c on c.id=b.sid
inner join A as c on c.id=b.sid
相似回答
