一个关于主从表的自连接查询

两个表 主表userinfo 从表postinfo 通过userid关联 一对多的关系
userinfo表
userid username

1	jecray
2	yang
3	chen
4	yc
NULL	NULL

postinfo表
PostID| Title |userid|content  |order

1	title1	1	content1	5
2	title2	2	content2	4
3	title3	1	content3	2
4	title4	1	content4	1
5	title5	3	content5	5
6	title6	3	content6	8
7	title7	4	content7	1
8	title8	2	content8	2
9	title9	2	content9	8
NULL	NULL	NULL	NULL	NULL

要求: 通过一条sql语句得出 每个用户发表post的orderid值最大的记录
如果orderid是时间就是得出每个用户发表最新帖子的相关信息, 该条信息包括两个表中的信息.

期望结果如下
postid   title     userid   content   orderid   username
1        title1        1    content1      5       jecray    
6        title6        3    content6      8       chen      
7        title7        4    content7      1       yc        
9        title9        2    content9      8       yang   

分两步走:
首先通过sql自连接得出postinfo中的每个用户的orderid值最大的记录

SELECT DISTINCT a.*
FROM postinfo a  where a.postid IN
          (SELECT TOP 1 postinfo.postid
         FROM postinfo
         WHERE postinfo.userid = a.userid
         ORDER BY orderid DESC)

在每行的检索中匹配指定条件的记录, 如果换成top 2 可以检索头两条orderid最大的记录

然后进行userinfo和postinfo的连接

SELECT DISTINCT a.*,b.*
FROM postinfo a inner join userinfo b on a.postid IN
          (SELECT TOP 1 postinfo.postid
         FROM postinfo
         WHERE postinfo.userid = a.userid
         ORDER BY orderid DESC) and a.userid = b.userid

便可得出上述效果.自连接的效率不知道怎么样, 反正不会很快了.

如果在oracle里面 还可以通过这样的形式实现

select * from postinfo where (uerid,orderid) in(select userid,max(orderid) as orderid from postinfo group by USERID)

可惜在sqlserver中不能通过

只要涉及到一张表中的同个字段之间或不同字段之间具有逻辑关系 就有可能用到自连接查询