求一条sql语句，两表合并，只取右表按时间排序第一条 - 编程论坛

#2

nhhykly9d2014-03-10 23:10

自已回答了：SELECT a.aid,a.内容，（SELECT TOP 1 b.内容 FROM b WHERE b.aid = a.aid ORDER BY b.时间 DESC)AS time
FROM a

[ 本帖最后由 nhhykly9d 于 2014-3-10 23:12 编辑 ]

#3

nhhykly9d2014-03-11 10:16

上面只能得到aid,a.内容，b.内容d三个字
现在我想得到
a.aid,a.内容，b.内容，b.时间，这又怎样写呢，
条件是取b,以时间降序，只取一行

#4

volte2014-03-11 11:04

select top 1 a.aid,a.内容，b.内容，b.时间 from a join b on a.aid = b.aid order by b.时间 desc

#5

nhhykly9d2014-03-11 11:32

谢谢volte回答。我测试了 ,你给这个只能返回a表中一行，我要的是能返回a表中所有行，并且只返回b表中b.aid =a.aid 按时间降序的第一行，
请各位师兄再看看

[ 本帖最后由 nhhykly9d 于 2014-3-11 11:46 编辑 ]

#6

nhhykly9d2014-03-13 10:00

想做的项目由另外方法做了，结帖了。

#7

nhhykly9d2014-03-16 23:08

以下是我的最后做出来的结果：向各位师兄汇报一下

SELECT Dpost_2.postID, (SELECT TOP (1) 修改时间 FROM Drepost WHERE (postID = Dpost_2.postID) ORDER BY 修改时间 DESC) AS retime, (SELECT TOP (1) UserName FROM Drepost AS Drepost_1 WHERE (postID = Dpost_2.postID) ORDER BY 修改时间 DESC) AS rename, Dpost_1.创建时间, Dpost_1.题目, Dpost_1.UserName, Dpost_1.readnum, Dpost_1.postID AS Expr1, (SELECT COUNT(postID) AS Expr1 FROM Drepost AS Drepost_2 GROUP BY postID HAVING (postID = Dpost_2.postID)) AS postnum FROM Dpost AS Dpost_2 INNER JOIN Dpost AS Dpost_1 ON Dpost_2.postID = Dpost_1.postID WHERE (Dpost_2.版块ID = @版块ID) ORDER BY Dpost_1.创建时间 DESC

#8

白岳皓2014-04-14 11:01

#9

唐子爵2014-05-16 12:25

你这个select top 1 ...不就是只显示前面一行而已吗？所以就只显示第一行的数据