有一個學生的名字和年齡表如下如何將年齡值轉換為一個范圍
- 7-9
- 9-11
- 11-13
- 13-15
- 15-17
- 17-19
并獲得最多學生的年齡范圍
創建表：
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age FLOAT NOT NULL
);
插入值：
INSERT INTO students
VALUES
(1, 'Ryan', 12),
(2, 'Joanna', 12.5),
(3, 'James', 11),
(4, 'Karen', 10),
(5, 'Holmes', 11.2),
(6, 'Garry', 12.1),
(7, 'Justin', 14.5),
(8, 'Emma', 15),
(9, 'Andy', 10),
(10, 'Claren', 9.5),
(11, 'Dennis', 9),
(12, 'Henna', 16),
(13, 'Iwanka', 15.4),
(14, 'June', 8.1),
(15, 'Kamila', 7.5),
(16, 'Lance', 17);
預期輸出應在最大記錄數的范圍內：
Range | Count
10-12 | 5
uj5u.com熱心網友回復：
--構造數字范圍表。
CREATE TABLE age_range (
id serial,
agerange numrange
);
INSERT INTO age_range (agerange)
VALUES ('[7,9]'), ('[10,12]'), ('[13,15]'), ('[15,17]'), ('[17,19]');
--cte 帶視窗功能。
WITH a AS (
SELECT
age,
name,
agerange
FROM
students s,
age_range b
WHERE
age <@ agerange IS TRUE
)
SELECT
*,
count(agerange) OVER (PARTITION BY agerange)
FROM
a
ORDER BY
agerange,
name;
uj5u.com熱心網友回復：
CASE WHEN您可以嘗試為您的邏輯使用帶有運算式的聚合函式，然后用于ORDER BY COUNT DESC獲取最大記錄數
SELECT (CASE WHEN age BETWEEN 7 AND 9 THEN '7-9'
WHEN age BETWEEN 10 AND 12 THEN '10-12'
WHEN age BETWEEN 13 AND 15 THEN '13-15'
WHEN age BETWEEN 15 AND 17 THEN '15-17'
WHEN age BETWEEN 17 AND 19 THEN '17-19' END) as range,
COUNT(*) cnt
FROM students
GROUP BY CASE WHEN age BETWEEN 7 AND 9 THEN '7-9'
WHEN age BETWEEN 10 AND 12 THEN '10-12'
WHEN age BETWEEN 13 AND 15 THEN '13-15'
WHEN age BETWEEN 15 AND 17 THEN '15-17'
WHEN age BETWEEN 17 AND 19 THEN '17-19' END
ORDER BY COUNT(*) DESC
LIMIT 1
編輯
如果您的范圍編號具有邏輯并且您想要通用范圍解決方案
您可以嘗試使用generate_series范圍邏輯生成范圍編號，然后進行外部聯接。
對于您的示例資料，我將使用generate_series(7,17,2)創建一個范圍編號，您期望計算開始和結束編號
SELECT CONCAT(t1.startnum,'-',t1.endnum) as range,
COUNT(*) cnt
FROM students s
INNER JOIN (
SELECT v startnum,v 2 endnum
FROM generate_series(7,17,2) v
) t1 ON s.age BETWEEN t1.startnum AND t1.endnum
GROUP BY CONCAT(t1.startnum,'-',t1.endnum)
ORDER BY COUNT(*) DESC
LIMIT 1
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/463287.html
標籤：sql 数据库 PostgreSQL
上一篇：使用Prisma獲得唯一的嵌套值