文章表 Article
字段 ArticleID,Title,Content,AddTime
---------------------------------------
评论表 Comment
字段 CID,ArticleID,Content,Agree(支持人数),DisAgree(反对人数)
------------------------------------------现要查询评论最多的文章,并显示支持人数,反对人数
字段 ArticleID,Title,Content,AddTime
---------------------------------------
评论表 Comment
字段 CID,ArticleID,Content,Agree(支持人数),DisAgree(反对人数)
------------------------------------------现要查询评论最多的文章,并显示支持人数,反对人数
select top 1 m.Title , count(1) 评论次数, sum(n.Agree) 支持人数 , sum(n.DisAgree) 反对人数
from Article m , Comment n
where m.ArticleID = n.ArticleID
group by m.Title
order by 评论次数 desc
from Article m , Comment n
where m.ArticleID = n.ArticleID
group by m.Title
order by 评论次数 desc
create table Comment(CID int identity,ArticleID int,Content varchar(1000),Agree int,DisAgree int)insert Article
select 1,'a','aaa','20101011'
union all
select 2,'b','bbb','20101012'
union all
select 3,'c','ccc','20101013'insert Comment
select 1,'aaa',10,5
union all
select 1,'aaa',15,2
union all
select 1,'aaa',15,0
union all
select 2,'bbb',20,5
union all
select 2,'bbb',22,0
union all
select 3,'ccc',25,5
union all
select 3,'ccc',2,5select a.ArticleID,c.Title,a.Agree,a.DisAgree from (select ArticleID,sum(Agree+DisAgree) cnt,sum(Agree) Agree,sum(DisAgree) DisAgree from Comment group by ArticleID ) a
join
(select max(cnt) cnt from (select sum(Agree+DisAgree) cnt from Comment group by ArticleID) a
) b on a.cnt=b.cnt join Article c on a.ArticleID=c.ArticleID
from Article m , Comment n
where m.ArticleID = n.ArticleID
group by m.Title
order by 评论次数 desc
数据库是ACCESS 的,语句运行不正确 。语法错误,在 count(1) 评论次数 中
语法错误 操作符丢失 在查询表达式 ‘(count(1))评论次数’中
create table #Comment(CID int identity,ArticleID int,Content varchar(1000),Agree int,DisAgree int)insert #Article
select 1,'a','aaa','20101011'
union all
select 2,'b','bbb','20101012'
union all
select 3,'c','ccc','20101013'insert #Comment
select 1,'aaa',10,5
union all
select 1,'aaa',15,2
union all
select 1,'aaa',15,0
union all
select 2,'bbb',20,5
union all
select 2,'bbb',22,0
union all
select 3,'ccc',25,5
union all
select 3,'ccc',2,5select top 1 #Article.ArticleID,Title,#Article.Content,AddTime,SUM(isnull(Agree,0)) as agree, SUM(isnull(DisAgree,0)) as DisAgree
,count(*) as '评论次数' from #Article inner join #Comment
on #Article.ArticleID=#Comment.ArticleID group by #Article.ArticleID,Title,#Article.Content,AddTime
order by count(*) desc眼见为实,试一下吧。
from Article m , Comment n
where m.ArticleID = n.ArticleID
group by m.Title
order by 评论次数 desc
--文章表 Article
--字段 ArticleID,Title,Content,AddTime
---------------------------------------
--评论表 Comment
--字段 CID,ArticleID,Content,Agree(支持人数),DisAgree(反对人数)create table #Article
(
ArticleID int,
Title varchar(100),
[Content] varchar(2000),
AddTime datetime
)create table #Comment
(
CID int,
ArticleID int,
[Content] varchar(2000),
Agree int,
DisAgree int
)insert into #Article values(1,'test title1','test content1',getdate())
insert into #Article values(2,'test title2','test content2',getdate())
--insert the comment
insert into #Comment values(1,1,'test content10 comment',5,1)
insert into #Comment values(2,1,'test content11 comment',3,8)
insert into #Comment values(3,2,'test content20 comment',5,1)select a.[Content],sum(c.Agree) as Agree,sum(c.DisAgree) as DisAgree from #Article a
inner join #Comment c on a.ArticleID=c.ArticleID
where a.ArticleID
IN(
select TOP 1 ArticleID from #Comment
group by ArticleID
order by count(ArticleID) desc
)
group by a.[Content]
drop table #Article
drop table #Comment