三张表如下:
一.论坛版块表:bbsInfo,数据如下
BBSCAPTIONID BBSCAPTION BBSNAMEID
1 C001 资讯区 BN001
2 C001 资讯区 BN002
3 C001 资讯区 BN003
4 C002 休闲区 BN004
5 C002 休闲区 BN005
6 C002 休闲区 BN006
7 C002 休闲区 BN007
8 C002 休闲区 BN008
9 C003 管理区 BN009
10 C004 求助区 BN010 BBSNAMEID为版块编号,共10个版块二.论坛用户表:bbsUserRoleInfo,数据如下
ROLEID ROLENICKNAME
1 10001 jay一路北向
2 10002 jay一路北向1 三.论坛帖子表:bbsCardInfo,数据如下
BBSCAPTIONID BBSNAMEID ROLEID CARDID CARDTEXT CARDTIME
1 C001 BN001 10001 Z10001 期待啊.... 2009-7-15 10:57:30
2 C001 BN002 10001 Z10002 好帅哦.... 2009-7-15 10:57:30
3 C001 BN003 10001 Z10003 好看额.... 2009-7-15 10:57:30
4 C002 BN004 10002 Z10004 加精.... 2009-7-15 10:57:30
5 C003 BN009 10002 Z10005 aaaa 2009-7-15 10:57:30
6 C001 BN002 10001 Z10006 bbbb 2009-7-14 10:58:30
7 C001 BN002 10002 Z10007 期待啊1....2009-7-16 18:08:51
8 C001 BN001 10002 Z10008 期待啊2....2009-7-16 18:08:56
9 C001 BN001 10002 Z10009 期待啊3....2009-7-16 18:08:59CARDTEXT为帖子主题,CARDID为帖子ID现在我想查各版块(10个版块,版块没有帖子的发帖人,发帖时间,发帖主题为空)最后发帖人,发帖时间,发帖主题,
无奈用left join可以实现·但是查主题用户的话就有重复数据了·因为要根据用户ID和主题分组
代码如下:
select a.bbsCaptionId,a.bbsnameid,c.rolenickname,b.cardtext,max(b.cardtime)
from bbsInfo a left join bbsCardInfo b
on a.Bbsnameid = b.bbsnameid left join bbsUserRoleInfo c
on c.roleid = b.roleid
group by a.bbsCaptionId,a.bbsnameid,c.rolenickname,b.cardtext;最后求出来的数据成了这样:
BBSCAPTIONID BBSNAMEID ROLENICKNAME CARDTEXT MAX(B.CARDTIME)
1 C001 BN001 jay一路北向 期待啊.... 2009-7-16 11:11:33
2 C001 BN001 jay一路北向1 期待啊.... 2009-7-16 18:08:59
3 C001 BN002 jay一路北向 好帅哦.... 2009-7-15 10:57:30
4 C001 BN002 jay一路北向1 期待啊.... 2009-7-16 18:08:56
5 C001 BN003 jay一路北向 好看额.... 2009-7-15 10:57:30
6 C002 BN004 jay一路北向1 2009-7-15 10:57:30
7 C002 BN005
8 C002 BN006
9 C002 BN007
10 C002 BN008
11 C003 BN009 jay一路北向1 加精.... 2009-7-15 10:57:30
12 C004 BN010
一.论坛版块表:bbsInfo,数据如下
BBSCAPTIONID BBSCAPTION BBSNAMEID
1 C001 资讯区 BN001
2 C001 资讯区 BN002
3 C001 资讯区 BN003
4 C002 休闲区 BN004
5 C002 休闲区 BN005
6 C002 休闲区 BN006
7 C002 休闲区 BN007
8 C002 休闲区 BN008
9 C003 管理区 BN009
10 C004 求助区 BN010 BBSNAMEID为版块编号,共10个版块二.论坛用户表:bbsUserRoleInfo,数据如下
ROLEID ROLENICKNAME
1 10001 jay一路北向
2 10002 jay一路北向1 三.论坛帖子表:bbsCardInfo,数据如下
BBSCAPTIONID BBSNAMEID ROLEID CARDID CARDTEXT CARDTIME
1 C001 BN001 10001 Z10001 期待啊.... 2009-7-15 10:57:30
2 C001 BN002 10001 Z10002 好帅哦.... 2009-7-15 10:57:30
3 C001 BN003 10001 Z10003 好看额.... 2009-7-15 10:57:30
4 C002 BN004 10002 Z10004 加精.... 2009-7-15 10:57:30
5 C003 BN009 10002 Z10005 aaaa 2009-7-15 10:57:30
6 C001 BN002 10001 Z10006 bbbb 2009-7-14 10:58:30
7 C001 BN002 10002 Z10007 期待啊1....2009-7-16 18:08:51
8 C001 BN001 10002 Z10008 期待啊2....2009-7-16 18:08:56
9 C001 BN001 10002 Z10009 期待啊3....2009-7-16 18:08:59CARDTEXT为帖子主题,CARDID为帖子ID现在我想查各版块(10个版块,版块没有帖子的发帖人,发帖时间,发帖主题为空)最后发帖人,发帖时间,发帖主题,
无奈用left join可以实现·但是查主题用户的话就有重复数据了·因为要根据用户ID和主题分组
代码如下:
select a.bbsCaptionId,a.bbsnameid,c.rolenickname,b.cardtext,max(b.cardtime)
from bbsInfo a left join bbsCardInfo b
on a.Bbsnameid = b.bbsnameid left join bbsUserRoleInfo c
on c.roleid = b.roleid
group by a.bbsCaptionId,a.bbsnameid,c.rolenickname,b.cardtext;最后求出来的数据成了这样:
BBSCAPTIONID BBSNAMEID ROLENICKNAME CARDTEXT MAX(B.CARDTIME)
1 C001 BN001 jay一路北向 期待啊.... 2009-7-16 11:11:33
2 C001 BN001 jay一路北向1 期待啊.... 2009-7-16 18:08:59
3 C001 BN002 jay一路北向 好帅哦.... 2009-7-15 10:57:30
4 C001 BN002 jay一路北向1 期待啊.... 2009-7-16 18:08:56
5 C001 BN003 jay一路北向 好看额.... 2009-7-15 10:57:30
6 C002 BN004 jay一路北向1 2009-7-15 10:57:30
7 C002 BN005
8 C002 BN006
9 C002 BN007
10 C002 BN008
11 C003 BN009 jay一路北向1 加精.... 2009-7-15 10:57:30
12 C004 BN010
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货