34,589
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[文章表]
if object_id('[文章表]') is not null drop table [文章表]
go
create table [文章表]([ID] int,[cmNum] int,[aTitle] int)
insert [文章表]
select 1,2,45456 union all
select 2,1,456 union all
select 3,4,111 union all
select 4,3,156
--> 测试数据:[评论表]
if object_id('[评论表]') is not null drop table [评论表]
go
create table [评论表]([评论的文章ID] int,[评论标题] int,[time] int)
insert [评论表]
select 1,456,1 union all
select 1,1561,2 union all
select 2,1231,3 union all
select 3,1231,4 union all
select 3,1231,5 union all
select 3,1231,6 union all
select 3,1231,7 union all
select 4,1231,8 union all
select 4,1231,9 union all
select 4,1231,10
select * from [文章表]
select * from [评论表]
--==================
select top 3 A.ID,A.atitle,B.评论数量,B.[最新更新时间],C.[评论标题] as [最新评论标题]
from [文章表] A
inner join (select [评论的文章ID] as ID,count(评论标题)as 评论数量,max(time) as [最新更新时间] from [评论表] group by [评论的文章ID])B
on A.id = B.ID
inner join [评论表] C on B.ID = C.[评论的文章ID] and B.[最新更新时间] = C.time
order by B.评论数量 desc
--==================结果
/*
ID atitle 评论数量 最新更新时间 最新评论标题
3 111 4 7 1231
4 156 3 10 1231
1 45456 2 2 1561
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-01-27 02:23:59
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[文章表]
if object_id('[文章表]') is not null drop table [文章表]
go
create table [文章表]([ID] int,[cmNum] int,[aTitle] int)
insert [文章表]
select 1,2,45456 union all
select 2,1,456 union all
select 3,4,111 union all
select 4,3,156
--> 测试数据:[评论表]
if object_id('[评论表]') is not null drop table [评论表]
go
create table [评论表]([评论的文章ID] int,[评论标题] int,[time] int)
insert [评论表]
select 1,456,1 union all
select 1,1561,2 union all
select 2,1231,3 union all
select 3,1231,4 union all
select 3,1231,5 union all
select 3,1231,6 union all
select 3,1231,7 union all
select 4,1231,8 union all
select 4,1231,9 union all
select 4,1231,10
--------------开始查询--------------------------
select
top 3 a.aTitle,b.评论标题
from
[文章表] a,[评论表] b
where
a.ID=b.评论的文章ID
and
b.time=(select max([time]) from [评论表] where 评论的文章ID=b.评论的文章ID)
----------------结果----------------------------
/*aTitle 评论标题
----------- -----------
156 1231
111 1231
456 1231
(3 行受影响)
*/