SQL SERVER 2005实例

  要求：根据numofreplies分组，0~100分一组，100~200分一组，200以上分一组，统计每组的总数
思路：在numofreplies里面先进性分组，然后再统计

SELECT
(case
when numOfreplies>=0 and numofreplies<=100 then '0-100'
when numOfreplies>100 and numofreplies<=200 then '100-200'
else '200+'
end) as Reply,
--按照要求进行分组，生成新的一列reply
count(0) as num
FROM thread_2008 with (nolock)
WHERE *****
group by
(case
when numOfreplies>=0 and numofreplies<=100 then '0-100'
when numOfreplies>100 and numofreplies<=200 then '100-200'
else '200+'
end)
--这里必须将原来的分组内容拷下来，sql不支持用别名替代
ORDER BY count(0)