请教个SQL语句

[cwbok]

ID GoodsID CommentClass
2 109 5
3 27 5
4 27 3
5 27 3

goodsid 是产品ID COMMENTCLASS 是评论级数


现在要按commentclass 数字相加最多的产品来排序，请问要怎么写???

[fengyun817]

WITH t1
     AS (SELECT goodsid,
                Sum(CommentClass) num
         FROM   table_name
         GROUP  BY goodsid)
SELECT    a.ID,
          a.GoodsID,
          a.CommentClass
FROM      table_name a
LEFT JOIN t1 b ON a.goodsid = b.goodsid
ORDER     BY b.num desc 

[挨踢直男]

select * ,(
  select sum( commentclass ) 
     from 你的这张表名 as b 
     where a.产品的id = b.goodsid 
) as sortcol 
  from goods as a order by sortcol desc

试试看

[cwbok]

能把 GOODSID 索引一下 统计吗。 最好不要跨表，产品表的记录好多啊。

[一把编程的菜刀]

你要根据评论排序产品，不跨表肯定不行的 你产品表和评论本来就是分开2个表的

[lhprince1]

select goods.goodsid,goods.goodsname,sortedgoodscomments.allcomments from goods,
(select goodsid,SUM(commentclass) as allcomments from goodscomments group by goodsid) as sortedgoodscomments 
where goods.goodsid=sortedgoodscomments.goodsid order by sortedgoodscomments.allcomments

[lhprince1]

像上面那么写，查询每条记录的时候是不是重新生成sortedgoodscomments?

[aben402]

SELECT goodsid,SUM(commentclass) AS grade FROM goods GROUP BY goodsid ORDER BY grade DESC;