主题表
subjectID 主键
userName
nickName
subjectType
subjectNature
subjectTitle
subjectContent
issueTime回帖表
replyID 主键
userName
replyTitle
replyContent
replyTime现在我要根据subjectID的值,查询出userName,nickName,subjectType,subjectNature,subjectTitle,subjectContent,
issueTime,并且根据查询出的userName值查询出回帖数量 即count(replyID)。有没高手会哟,自己想了半天没想出来。
subjectID 主键
userName
nickName
subjectType
subjectNature
subjectTitle
subjectContent
issueTime回帖表
replyID 主键
userName
replyTitle
replyContent
replyTime现在我要根据subjectID的值,查询出userName,nickName,subjectType,subjectNature,subjectTitle,subjectContent,
issueTime,并且根据查询出的userName值查询出回帖数量 即count(replyID)。有没高手会哟,自己想了半天没想出来。
A.userName,nickName,subjectType,subjectNature,subjectTitle,subjectContent,
issueTime,B.replyCount
FROM 主题表 AS A
JOIN (SELECT userName,COUNT(*) AS replyCount FROM 回帖表
GROUP BY userName) AS B
ON A.userName = B.userName
WHERE subjectID = ?
select
*
,(select count(replyID) from 回帖表 where 回帖表.userName=主题表.userName)as 回帖数量
from 主题表
(
select * from 主题表 where subjectID='你输入的值'
) T
join 回帖表 H
on T.userName=H.userName
a.userName,a.nickName,a.subjectType,a.subjectNature,a.subjectTitle,a.subjectContent,a.issueTime,
isnull(count(b.*),0) as 回帖数量
from
主题表 a
left join
回帖表 b
on
a.userName=b.userName
group by
a.userName,a.nickName,a.subjectType,a.subjectNature,a.subjectTitle,a.subjectContent,a.issueTime
select t.*,isnull((select count(1) from 回帖表 m where m.replyID = t.subjectID),0) 回帖数量 from 主题表 t
select t.*,isnull((select count(1) from 回帖表 m where m.userName = t.userName),0) 回帖数量 from 主题表 t
A.issueTime,count(replyID) 次数 from
(
select * from 主题表 where subjectID='你输入的值'
) T
join 回帖表 H
on T.userName=H.userName group by A.userName,A.nickName,A.subjectType,A.subjectNature,A.subjectTitle,A.subjectContent,
A.issueTime
a.*,b.replyCount
from
主题表 a
join
(select userName,count(1) as replyCount from 回帖表 group by userName)b
on
a.userName = b.userName
where
subjectID = 你的值
To: liangCK2 楼 和 3楼 的写法那个效率更好一点?
谢谢Tony.三楼要逐条判断,二楼是通过什么方法取得结果的?
当userName没有回帖 即回帖表里没有用户记录时,查询结果全部为空,这怎么办啊?
我想没回帖时,前面的查询结果出来,replyCount为空。
A.userName,nickName,subjectType,subjectNature,subjectTitle,subjectContent,
issueTime,B.replyCount
FROM 主题表 AS A
JOIN (SELECT userName,isnull(COUNT(*),0) AS replyCount FROM 回帖表
GROUP BY userName) AS B
ON A.userName = B.userName
WHERE subjectID = ?
A.issueTime,isnull(count(replyID),0) 次数 from
(
select * from 主题表 where subjectID='你输入的值'
) T
join 回帖表 H
on T.userName=H.userName group by A.userName,A.nickName,A.subjectType,A.subjectNature,A.subjectTitle,A.subjectContent,
A.issueTime
A.userName,nickName,subjectType,subjectNature,subjectTitle,subjectContent,
issueTime,ISNULL(B.replyCount,0) AS replyCount
FROM 主题表 AS A
LEFT JOIN (SELECT userName,COUNT(*) AS replyCount FROM 回帖表
GROUP BY userName) AS B
ON A.userName = B.userName
WHERE subjectID = ?
To Tony.计划显示是一样的。不过,执行起来因该像你说的那样。
自己建表实验了一下。即使两个表相差上百倍,二楼和三楼的速度也不相上下。跟想象的不一样。