我有两个表StudentTable和LevelsTable。我正在使用Java和Sqlite。
我的第一张桌子：StudentTable
+----+-----------+-------------+-----------+---------------+
| idS| firstName | famillyName | age | pushUps |
+----+-----------+-------------+-----------+---------------+
| 1 | a | d | 17 | 20 |
| 2 | b | e | 18 | 30 |
| 3 | c | f | 19 | 50 |
+----+-----------+-------------+-----------+---------------+
我的第二张表：LevelsTable
+----+-----------+--------+-----------+--------+--------------+
| idP| veryWeak | weak | average | good | veryGood |
+----+-----------+--------+-----------+--------+--------------+
| 1 | 10 | 15 | 20 | 30 | 40 |
+----+-----------+--------+-----------+--------+--------------+
我想根据每个级别做多少俯卧撑来计算每个级别的学生人数。
例如：如果我有1000名学生，我想得到这样的结果：
100个学生∈[10,15 [->非常弱
250个学生∈[15,20 [->弱
400名学生ε[20,30 [->平均
150名学生∈[30,40 [->好
100名学生> 40->很好。
您对解决方案有何建议？
最佳答案
使用纯SQL，您可以这样做：
SELECT CASE x.studentLevel WHEN 0 THEN 'super-duper weak'
WHEN 1 THEN 'very weak'
WHEN 2 THEN 'weak'
WHEN 3 THEN 'average'
WHEN 4 THEN 'good'
WHEN 5 THEN 'very good'
END AS "level"
, COUNT(*) AS "count"
FROM ( SELECT CASE WHEN s.pushUps >= lvl.veryGood THEN 5
WHEN s.pushUps >= lvl.good THEN 4
WHEN s.pushUps >= lvl.average THEN 3
WHEN s.pushUps >= lvl.weak THEN 2
WHEN s.pushUps >= lvl.veryWeak THEN 1
ELSE 0
END AS studentLevel
FROM StudentTable s
, LevelsTable lvl
WHERE lvl.idP = 1/*pushUps*/
) x
GROUP BY x.studentLevel
ORDER BY x.studentLevel
更新资料
正如我在评论中提到的那样，您的
LevelsTable对于SQL来说不是很方便。草莓公司在另一个答案中建议的表格是朝正确方向迈出的一步，但需要进行两个更改：它需要多组级别，并且级别应为范围，并带有上下边界。对于多组范围，您需要一列来标识该组。让我们称之为
levelType，并且为了使其简单易用，我们将其命名为文本列，例如类型。 'pushUps'。对于范围边界，一种方法是上下限，例如
0-9，10-19等。如果您的值可以是浮点数，那么这将行不通，因为9.5会在范围之间，因此最好像问题中所描述的那样，将边界设置为较低范围和较高范围。您可以根据需要保留
idP列，但这不是必需的。CREATE TABLE LevelsTable (
levelType VARCHAR(30) NOT NULL,
lowerLevel INTEGER NOT NULL,
upperLevel INTEGER NULL,
levelDesc VARCHAR(30) NOT NULL,
CONSTRAINT PK_LevelsTable PRIMARY KEY ( levelType, lowerLevel )
);
INSERT INTO LevelsTable VALUES ( 'pushUps', 0, 10 , 'pathetic' );
INSERT INTO LevelsTable VALUES ( 'pushUps', 10, 15 , 'very weak' );
INSERT INTO LevelsTable VALUES ( 'pushUps', 15, 20 , 'weak' );
INSERT INTO LevelsTable VALUES ( 'pushUps', 20, 30 , 'average' );
INSERT INTO LevelsTable VALUES ( 'pushUps', 30, 40 , 'good' );
INSERT INTO LevelsTable VALUES ( 'pushUps', 40, NULL, 'very good' );
INSERT INTO LevelsTable VALUES ( 'age' , 0, 13 , 'child' );
INSERT INTO LevelsTable VALUES ( 'age' , 13, 20 , 'teenager' );
INSERT INTO LevelsTable VALUES ( 'age' , 20, 55 , 'adult' );
INSERT INTO LevelsTable VALUES ( 'age' , 55, NULL, 'senior' );
现在，如果要列出学生并显示他们的水平，这很简单：
SELECT s.idS, s.firstName, s.famillyName
, s.age, a.levelDesc AS ageLevel
, s.pushUps, p.levelDesc AS pushUpLevel
FROM StudentTable s
JOIN LevelsTable a ON a.levelType = 'age'
AND a.lowerLevel <= s.age
AND (a.upperLevel > s.age OR a.upperLevel IS NULL)
JOIN LevelsTable p ON p.levelType = 'pushUps'
AND p.lowerLevel <= s.pushUps
AND (p.upperLevel > s.pushUps OR p.upperLevel IS NULL)
ORDER BY s.idS;
输出为：
+----+-----------+-------------+-----+----------+---------+-------------+
| idS| firstName | famillyName | age | ageLevel | pushUps | pushUpLevel |
+----+-----------+-------------+-----+----------+---------+-------------+
| 1 | a | d | 17 | teenager | 20 | average |
| 2 | b | e | 18 | teenager | 30 | good |
| 3 | c | f | 19 | teenager | 50 | very good |
+----+-----------+-------------+-----+----------+---------+-------------+
俯卧撑组计数查询为：
SELECT lvl.lowerLevel AS "from", lvl.upperLevel AS "to"
, lvl.levelDesc AS "level", COUNT(*) AS "students"
FROM StudentTable s
JOIN LevelsTable lvl ON lvl.levelType = 'pushUps'
AND lvl.lowerLevel <= s.pushUps
AND (lvl.upperLevel > s.pushUps OR lvl.upperLevel
GROUP BY lvl.lowerLevel, lvl.upperLevel, lvl.levelDesc
ORDER BY lvl.lowerLevel;
输出为：
+------+----+-----------+----------+
| from | to | level | students |
+------+----+-----------+----------+
| 20 | 30 | average | 1 |
| 30 | 40 | good | 1 |
| 40 | | very good | 1 |
+------+----+-----------+----------+