日期:2014-05-18  浏览次数:20414 次

sql语句 查询某条数据在几个阶段的总数
1-10是个阶段10-20,20-30这样往后增加 我要查询1-10之间的总数10-20之间的总数  
SELECT COUNT('level') FROM user_role WHERE `level` BETWEEN 1 AND 10 UNION 
SELECT COUNT('level') FROM user_role WHERE `level` BETWEEN 10 AND 20
这样写阶段多了 要加很多 如何写更简洁

------解决方案--------------------
SQL code

DECLARE @TB TABLE([Level] INT)
INSERT INTO @TB
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 10 UNION ALL
SELECT 11 

SELECT 级别,COUNT([Level]) LEVEL_COUNT FROM (
SELECT [Level],CASE WHEN ([Level]%10<>0) THEN ([Level]/10+1) ELSE ([Level]/10) END AS '级别' FROM @TB
)X
GROUP BY 级别

--------结果
级别   LEVEL_COUNT
1    4
2    2
3    1