- 论坛徽章:
- 0
|
不知道这个是不是你想要的,以scott.emp为例,
分区段不统计
SELECT CASE
WHEN sal>=0 AND sal<1000 THEN '0~1000'
WHEN sal>=1000 AND sal<2000 THEN '1000~2000'
WHEN sal>=2000 AND sal<3000 THEN '2000~3000'
WHEN sal>=3000 AND sal<4000 THEN '3000~4000'
WHEN sal>=4000 AND sal<5000 THEN '4000~5000'
WHEN sal>=5000 AND sal<6000 THEN '5000~6000'
END "区间段",COUNT(*)
FROM scott.emp
GROUP BY (CASE
WHEN sal>=0 AND sal<1000 THEN '0~1000'
WHEN sal>=1000 AND sal<2000 THEN '1000~2000'
WHEN sal>=2000 AND sal<3000 THEN '2000~3000'
WHEN sal>=3000 AND sal<4000 THEN '3000~4000'
WHEN sal>=4000 AND sal<5000 THEN '4000~5000'
WHEN sal>=5000 AND sal<6000 THEN '5000~6000'
END);
各部门分区段统计(做小计和统计)
SELECT deptno,CASE
WHEN sal>=0 AND sal<1000 THEN '0~1000'
WHEN sal>=1000 AND sal<2000 THEN '1000~2000'
WHEN sal>=2000 AND sal<3000 THEN '2000~3000'
WHEN sal>=3000 AND sal<4000 THEN '3000~4000'
WHEN sal>=4000 AND sal<5000 THEN '4000~5000'
WHEN sal>=5000 AND sal<6000 THEN '5000~6000'
END "区间段",COUNT(*)
FROM scott.emp
GROUP BY rollup(deptno,(CASE
WHEN sal>=0 AND sal<1000 THEN '0~1000'
WHEN sal>=1000 AND sal<2000 THEN '1000~2000'
WHEN sal>=2000 AND sal<3000 THEN '2000~3000'
WHEN sal>=3000 AND sal<4000 THEN '3000~4000'
WHEN sal>=4000 AND sal<5000 THEN '4000~5000'
WHEN sal>=5000 AND sal<6000 THEN '5000~6000'
END)); |
|