有个学生表如下所示，想分组返回接口数据 如90-100的学生是张三、李四；80-89的学生是王五、王六；想请教下 这样的sql如何写 数据库是sqlserver
| 学生姓名 | 分数 |
|---|---|
| 张三 | 90 |
| 李四 | 91 |
| 王五 | 82 |
| 王六 | 85 |
SELECT
CASE
WHEN score BETWEEN 90 AND 100 THEN '90-100'
WHEN score BETWEEN 80 AND 89 THEN '80-89'
ELSE 'below 80'
END AS grade_range,
STRING_AGG(name, ',') AS students
FROM
student
GROUP BY
CASE
WHEN score BETWEEN 90 AND 100 THEN '90-100'
WHEN score BETWEEN 80 AND 89 THEN '80-89'
ELSE 'below 80'
END;
SELECT
CASE
WHEN score BETWEEN 90 AND 100 THEN '90-100'
WHEN score BETWEEN 80 AND 89 THEN '80-89'
ELSE 'below 80'
END AS grade_range,
STRING_AGG(name, ',') AS students
FROM
student
GROUP BY
CASE
WHEN score BETWEEN 90 AND 100 THEN '90-100'
WHEN score BETWEEN 80 AND 89 THEN '80-89'
ELSE 'below 80'
END;
SELECT
CONCAT((FLOOR(score/10)*10), ' - ', ((FLOOR(score/10)*10)+9)) AS grade_range,
STRING_AGG(name, ',') AS students
FROM
student
GROUP BY
FLOOR(score/10);
这样吗，每十分一个分组
SELECT
CONCAT(
'{ "name": "', grade_range, '", "children": [',
STRING_AGG('{"name": "' + name + '", "score": ' + CAST(score AS VARCHAR(10)) + '}', ','),
'] }'
) AS result
FROM
(
SELECT
CASE
WHEN score BETWEEN 90 AND 100 THEN '90-100'
WHEN score BETWEEN 80 AND 89 THEN '80-89'
ELSE 'below 80'
END AS grade_range,
name,
score
FROM
student
) AS subquery
GROUP BY
grade_range;
这种直接用程序生成不就好了
请登录
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：