表的结构
表1:riji (id,memberId,content等)
表2:riji_comment(id,commentContent,rijiId)
问题一:查找10条评论数最多的日记
建视图countComentByRijiId
SELECT
COUNT
(
*
)
AS
MaxCol, rijiId
FROM dbo.riji_comment
GROUP BY rijiId
FROM dbo.riji_comment
GROUP BY rijiId
查询:
select
*
from
riji
where
id
in
(
select
top
10
rijiId
from
countComentByRijiId
order
by
MaxCol
desc
)
问题二:查找日记评论数最多的日记id
建试图
SELECT
MAX
(MaxCol)
AS
MaxCol
FROM dbo.countComentByRijiId
FROM dbo.countComentByRijiId
查询:
Select
v1.MaxCol,t1.rijiId
from
countComentByRijiId t1, v1
where
t1.MaxCol
=
v1.MaxCol