如何编写查询语句,查询各个子论坛中最新发表的主题TopicID ForumID LastReplier LastReplyTime
1 1 A1 2010-09-16 16:32:37.347
2 1 A2 2010-09-15 16:47:40.750
3 1 A3 2010-09-17 15:29:41.983
4 2 A1 2010-09-13 12:19:21.983
5 2 A3 2010-09-13 13:20:34.750如何编写高效率的查询语句,根据ForumID分组,查询出最新发表的主题查询后的结果应该如下:
TopicID ForumID LastReplier LastReplyTime
3 1 A3 2010-09-17 15:29:41.983
5 2 A3 2010-09-13 13:20:34.750
1 1 A1 2010-09-16 16:32:37.347
2 1 A2 2010-09-15 16:47:40.750
3 1 A3 2010-09-17 15:29:41.983
4 2 A1 2010-09-13 12:19:21.983
5 2 A3 2010-09-13 13:20:34.750如何编写高效率的查询语句,根据ForumID分组,查询出最新发表的主题查询后的结果应该如下:
TopicID ForumID LastReplier LastReplyTime
3 1 A3 2010-09-17 15:29:41.983
5 2 A3 2010-09-13 13:20:34.750
from tb t
where LastReplyTime=(select max(LastReplyTime)
from tb
where orumID =t.orumID )
from group by forumid
drop table tb
Go
Create table tb([TopicID] int,[ForumID] int,[LastReplier] nvarchar(2),[LastReplyTime]datetime)
Insert tb
select 1,1,N'A1',N'2010-09-16 16:32:37.347' union all
select 2,1,N'A2',N'2010-09-15 16:47:40.750' union all
select 3,1,N'A3',N'2010-09-17 15:29:41.983' union all
select 4,2,N'A1',N'2010-09-13 12:19:21.983' union all
select 5,2,N'A3',N'2010-09-13 13:20:34.750'
Go
select *
from tb t
where LastReplyTime=(select max(LastReplyTime)
from tb
where forumID =t.forumID )
/*
TopicID ForumID LastReplier LastReplyTime
----------- ----------- ----------- -----------------------
5 2 A3 2010-09-13 13:20:34.750
3 1 A3 2010-09-17 15:29:41.983
*/
if not object_id('tp') is null
drop table tpcreate table tp
(
TopicID int,
ForumID int,
LastReplier varchar(10),
LastReplyTime datetime
)
insert into tp select 1,1,'A1','2010-09-16 16:32:37.347'
union all select 2,1,'A2','2010-09-15 16:47:40.750'
union all select 3,1,'A3','2010-09-17 15:29:41.983'
union all select 4,2,'A1','2010-09-13 12:19:21.983'
union all select 5,2,'A3','2010-09-13 13:20:34.750'select * from tp b where not exists
(select * from tp where ForumID=b.ForumID and LastReplyTime>b.LastReplyTime)TopicID ForumID LastReplier LastReplyTime
----------- ----------- ----------- -----------------------
3 1 A3 2010-09-17 15:29:41.983
5 2 A3 2010-09-13 13:20:34.750(2 行受影响)
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([TopicID] int,[ForumID] int,[LastReplier] varchar(2),[LastReplyTime] datetime)
insert [TB]
select 1,1,'A1','2010-09-16 16:32:37.347' union all
select 2,1,'A2','2010-09-15 16:47:40.750' union all
select 3,1,'A3','2010-09-17 15:29:41.983' union all
select 4,2,'A1','2010-09-13 12:19:21.983' union all
select 5,2,'A3','2010-09-13 13:20:34.750'
GO--> 查询结果
SELECT a.TopicID,a.ForumID,a.LastReplier,a.LastReplyTime
FROM [TB] a
where a.LastReplyTime = (select MAX(LastReplyTime)
from TB where ForumID = a.ForumID group by ForumID )
--> 删除表格
--DROP TABLE [TB]
(select * from tp where ForumID=b.ForumID and LastReplyTime>b.LastReplyTime)
比select *
from tb t
where LastReplyTime=(select max(LastReplyTime)
from tb
where forumID =t.forumID )因为用了Exists判断,效率是不是高一些?
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([TopicID] int,[ForumID] int,[LastReplier] varchar(2),[LastReplyTime] datetime)
insert [TB]
select 1,1,'A1','2010-09-16 16:32:37.347' union all
select 2,1,'A2','2010-09-15 16:47:40.750' union all
select 3,1,'A3','2010-09-17 15:29:41.983' union all
select 4,2,'A1','2010-09-13 12:19:21.983' union all
select 5,2,'A3','2010-09-13 13:20:34.750'
GO
;with tt as (select id = row_number() over (partition by [ForumID] order by [LastReplyTime] desc ),* from [TB])
select * from tt where id = 1
where lastreplytime=(select max(lastreplytime) from tb where orumid=t.orumid)