SELECT article.*,member.user,category.catname,category.id,count(comment.id) AS comrate FROM `article`,`member`,`category`,`comment` WHERE comment.artid = article.id AND article.typeid=3 AND article.catid = category.id AND article.authorid = member.id ORDER BY count(comment.id) DESC
SELECT article.*,member.user,category.catname,category.id FROM `article`,`member`,`category` WHERE article.typeid=3 AND article.catid = category.id AND article.authorid = member.id ORDER BY (SELECT COUNT(*) FROM `comment` WHERE artid = article.id) DESC语法上没有问题,你的错误提示是什么?
分下组吧 SELECT article.*,member.user,category.catname,category.id,count(comment.id) AS comrate FROM `article`,`member`,`category`,`comment` WHERE comment.artid = article.id AND article.typeid=3 AND article.catid = category.id AND article.authorid = member.idgroup by //随便哪个字段都差不多,看你设计
ORDER BY comrate DESC
前面SELECT里含有count(comment.id) AS comrate的sql语句就不能被执行了,我改成comment.id就可以执行了,但是执行后,就是成了只显示满足comment.artid = article.id的记录了,其他不满足的记录则不显示了。
SELECT article.*,member.user,category.catname,category.id FROM `article`,`member`,`category` WHERE article.typeid=3 AND article.catid = category.id AND article.authorid = member.id ORDER BY (SELECT COUNT(*) FROM `comment` WHERE artid = article.id) DESC你的这段SQL没有问题。 你的错误提示是什么? 贴出你的 show create table article; show create table member; show create table category; show create table comment;
FROM `article`,`member`,`category`,`comment`
WHERE comment.artid = article.id AND article.typeid=3
AND article.catid = category.id AND article.authorid = member.id
ORDER BY count(comment.id) DESC
FROM `article`,`member`,`category`
WHERE article.typeid=3
AND article.catid = category.id AND article.authorid = member.id
ORDER BY (SELECT COUNT(*) FROM `comment` WHERE artid = article.id) DESC语法上没有问题,你的错误提示是什么?
SELECT article.*,member.user,category.catname,category.id,count(comment.id) AS comrate
FROM `article`,`member`,`category`,`comment`
WHERE comment.artid = article.id AND article.typeid=3
AND article.catid = category.id AND article.authorid = member.idgroup by //随便哪个字段都差不多,看你设计
ORDER BY comrate DESC
前面SELECT里含有count(comment.id) AS comrate的sql语句就不能被执行了,我改成comment.id就可以执行了,但是执行后,就是成了只显示满足comment.artid = article.id的记录了,其他不满足的记录则不显示了。
你用的是内连接,用LEFT JOIN OR RIGHT JOIN 试试
分组后,还是有点问题,出来的记录只有匹配comment.artid= article.id的才显示,不匹配的则不显示,我其实要实现的效果就是我最初写的那段。
FROM `article`,`member`,`category`
WHERE article.typeid=3
AND article.catid = category.id AND article.authorid = member.id
ORDER BY (SELECT COUNT(*) FROM `comment` WHERE artid = article.id) DESC你的这段SQL没有问题。
你的错误提示是什么?
贴出你的
show create table article;
show create table member;
show create table category;
show create table comment;