Question

Oracle查询：用一条语句查询出0-10分；11-20分；21-30分....到91-100分的记录数；

也就是说0到10分有多少人；11到20分有多少人....91到100分有多少人！只能用一条语句查询出来！

Answer 1

SELECT
CASE
WHEN 分数 >=0 AND 分数 <= 10 THEN '0-10分'
WHEN 分数 >=11 AND 分数 <= 20 THEN '11-20分'
WHEN 分数 >=21 AND 分数 <= 30 THEN '21-30分'
WHEN 分数 >=31 AND 分数 <= 40 THEN '31-40分'
WHEN 分数 >=41 AND 分数 <= 50 THEN '41-50分'
WHEN 分数 >=51 AND 分数 <= 60 THEN '51-60分'
WHEN 分数 >=61 AND 分数 <= 70 THEN '61-70分'
WHEN 分数 >=71 AND 分数 <= 80 THEN '71-80分'
WHEN 分数 >=81 AND 分数 <= 90 THEN '81-90分'
ELSE '91-100分'
END AS 分数区间,
COUNT(1) AS 记录数
FROM
..
WHERE
..
GROUP BY
CASE
WHEN 分数 >=0 AND 分数 <= 10 THEN '0-10分'
WHEN 分数 >=11 AND 分数 <= 20 THEN '11-20分'
WHEN 分数 >=21 AND 分数 <= 30 THEN '21-30分'
WHEN 分数 >=31 AND 分数 <= 40 THEN '31-40分'
WHEN 分数 >=41 AND 分数 <= 50 THEN '41-50分'
WHEN 分数 >=51 AND 分数 <= 60 THEN '51-60分'
WHEN 分数 >=61 AND 分数 <= 70 THEN '61-70分'
WHEN 分数 >=71 AND 分数 <= 80 THEN '71-80分'
WHEN 分数 >=81 AND 分数 <= 90 THEN '81-90分'
ELSE '91-100分'
END

Answer 2

假设成绩字段为score
SELECT SUM(CASE WHEN score BETWEEN 0 AND 10 THEN 1 ELSE 0 END) AS '0-10',
SUM(CASE WHEN score BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS '11-20',
SUM(CASE WHEN score BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS '21-30',
SUM(CASE WHEN score BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '31-40',
SUM(CASE WHEN score BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '41-50',
SUM(CASE WHEN score BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS '51-60',
SUM(CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '61-70',
SUM(CASE WHEN score BETWEEN 71 AND 80 THEN 1 ELSE 0 END) AS '71-80',
SUM(CASE WHEN score BETWEEN 81 AND 90 THEN 1 ELSE 0 END) AS '81-90',
SUM(CASE WHEN score BETWEEN 91 AND 100 THEN 1 ELSE 0 END) AS '91-100'
FROM tab

Answer 3

楼上的好像还要GROUP BY 吧！

Answer 4

no

Answer 5

select count(1) from (
select a.countname from books a
where a.iscount =0
union all
select distinct b.countname from books b
where B.iscount = 1)；
试试这个，大数据量的话速度可能会慢
不过你这个表设计的有问题，建议你如果非系列的图书，countname字段也不要为空，填上书名，这样就用一个 distinct countname 就出来了，速度要快很多
请参考