选择查询中的号码组

Question

我有一个表，其中保存用户编号及其“分数”。

user_number | score
  0832824       6
  0478233       3
    ...        ...

分数从 3 到 15。我想创建一个查询来获取分数以及具有该分数的用户总数。但我需要制作四组不同的分数：12 到 15、8 到 11、5 到 7 和小于 5。

有点像这样：

  score  | total_users
12 to 15      5000
 8 to 11      3000
   ...         ...

感谢您提供的任何答案！

Answer 1

SELECT t.range AS score, COUNT(*) AS total_users from 
(   
    SELECT CASE WHEN score BETWEEN 12 AND 15 THEN '12 to 15'     
                WHEN score BETWEEN 8 AND 11 THEN '8 to 11' 
                WHEN score BETWEEN 5 AND 7 THEN '5 to 7'    
                WHEN score < 5 THEN 'less than 5' 
           END AS range   
    FROM scores) t 
GROUP BY t.range 

Answer 2

您可以使用 BETWEEN 子句单独选择每个组，并使用组合结果UNION

SELECT  score = '12 to 15', total_users = COUNT(*)
FROM    ATable
WHERE   score BETWEEN 12 AND 15
UNION ALL
SELECT  score = '8 to 11', total_users = COUNT(*)
FROM    ATable
WHERE   score BETWEEN 8 AND 11
UNION ALL
SELECT  score = '5 to 7', total_users = COUNT(*)
FROM    ATable
WHERE   score BETWEEN 5 AND 7
UNION ALL
SELECT  score = 'less than 5', total_users = COUNT(*)
FROM    ATable
WHERE   score < 5

Answer 3

SELECT SUM(CASE WHEN score BETWEEN 12 AND 15 THEN 1 ELSE 0 END) AS [12-15],
       SUM(CASE WHEN score BETWEEN 8 AND 11 THEN 1 ELSE 0 END) AS [8-11],
       SUM(CASE WHEN score BETWEEN 5 AND 7 THEN 1 ELSE 0 END) AS [5-7],
       SUM(CASE WHEN score < 5 THEN 1 ELSE 0 END) AS [<5]
    FROM YourTable

Answer 4

CREATE TABLE scores
(
   score         NUMBER,
   user_number   VARCHAR2 (20)
);

INSERT INTO scores
     VALUES (3, '000001');

INSERT INTO scores
     VALUES (4, '000002');

INSERT INTO scores
     VALUES (4, '000003');

INSERT INTO scores
     VALUES (12, '000005');

COMMIT;

  SELECT score, COUNT (DISTINCT user_number) number_of_users
    FROM (SELECT CASE
                    WHEN score < 5 THEN 'Below 5'
                    WHEN score >= 5 AND score <= 7 THEN '5 to 7'
                    WHEN score >= 8 AND score <= 11 THEN '8 to 11'
                    WHEN score >= 12 AND score <= 15 THEN '12 to 15'
                    ELSE 'unclassified'
                 END
                    score,
                 user_number
            FROM scores)
GROUP BY score;