在线 高分求人帮写一条 SQL

[阿里帽子]

原来的SQL 如下

select top (" + shu + ") a.id,a.biaoti,a.shijian,a.dianjishu,a.neirong,[user].ming from (select ROW_NUMBER() over(order by [shijian] desc) as rowid ,* from [wen] where biaoti LIKE '%" + lanmuid + "%' or neirong LIKE '%" + lanmuid + "%' and zhuangtai = 0)as A INNER JOIN [user] ON userid=[user].id where rowid>(" + shu + "*(" + ye + "))

模拟查询 目前用正常 但是现在要加多一个查询 就是查询出这个文章 里面有多少条评论 评论表是另外的
评论表
id
userid
wenid
neirong
shijian

怎么加上去？ 要查询到这个文章 有多少个评论 ....算和

[正怒月神]

select top (" + shu + ") a.id,a.biaoti,a.shijian,a.dianjishu,a.neirong,[user].ming from (select ROW_NUMBER() over(order by [shijian] desc) as rowid ,A.*,count(B.id) from [wen] where biaoti LIKE '%" + lanmuid + "%' or neirong LIKE '%" + lanmuid + "%' and zhuangtai = 0)as A INNER JOIN [user] ON userid=[user].id join 评论表 as B on A.wenid=B.wenid where rowid>(" + shu + "*(" + ye + ")) 你试试看，我没测试

[exception92]

inner join 评论表，使用count函数，最后group by 你的查询列即可。

[阿里帽子]

引用 1 楼 正怒月神 的回复:

select top (" + shu + ") a.id,a.biaoti,a.shijian,a.dianjishu,a.neirong,[user].ming from (select ROW_NUMBER() over(order by [shijian] desc) as rowid ,A.*,count(B.id) from [wen] where biaoti LIKE '%" + lanmuid + "%' or neirong LIKE '%" + lanmuid + "%' and zhuangtai = 0)as A INNER JOIN [user] ON userid=[user].id join 评论表 as B on A.wenid=B.wenid where rowid>(" + shu + "*(" + ye + ")) 你试试看，我没测试

不得。。

[正怒月神]

引用 3 楼 阿里帽子 的回复:

[quote=引用 1 楼 正怒月神 的回复:] select top (" + shu + ") a.id,a.biaoti,a.shijian,a.dianjishu,a.neirong,[user].ming from (select ROW_NUMBER() over(order by [shijian] desc) as rowid ,A.*,count(B.id) from [wen] where biaoti LIKE '%" + lanmuid + "%' or neirong LIKE '%" + lanmuid + "%' and zhuangtai = 0)as A INNER JOIN [user] ON userid=[user].id join 评论表 as B on A.wenid=B.wenid where rowid>(" + shu + "*(" + ye + ")) 你试试看，我没测试

不得。。[/quote] 那加个group