mysql SQL如果按组

juud5qan 于 12个月前发布在 Mysql

关注(0)|答案(2)|浏览(124)

我有这样的SQL表：

TABLE1
STUDENT SCORE   SCOREVAL
1   PASS    10
1   MOD1    4
1   MOD2    5
2   MOD1    4
3   MOD2    2
3   MOD2    1
3   MOD1    9
3   ER  1
4   MOD2    10
4   ER  6

字符串
我想把表格简化成这样

STUDENT SCORE
STUDENT SCORE   SCOREVAL
1   PASS    10
2   MOD1    4
3   PASS    11
4   MOD2    10

型
使用规则

For each STUDENT
if any value of **SCORE** equals to PASS, then is PASS
if contains value MOD1 and MOD2, then is PASS
if contains value MOD1, then is MOD1
if contains value MOD2, then is MOD2

型
然后对于评分，

if any value of SCORE equals to PASS, then take MAX(SCOREVAL[SCORE = PASS])
if contains value MOD1 and MOD2, then take MAX(SCOREVAL[SCORE = MOD1] + MAX(SCOREVAL[SCORE=MOD2])
if contains value MOD1, then take MAX(SCOREVAL[SCORE = MOD1])
if contains value MOD2, then take MAX(SCOREVAL[SCORE = MOD2])

型
我试试这个：

SELECT * 
FROM TABLE1 
GROUP BY STUDENT when SCORE = 'PASS' then 'PASS'
when SCORE = 'MOD1' and SCORE = 'MOD2' then 'PASS'
when SCORE = 'MOD1' then 'MOD1'
when SCORE = 'MOD2' then 'MOD2'
else EXEMPT
end

型
没有成功

mysql

来源：https://stackoverflow.com/questions/77655601/sql-ifelse-by-group

2条答案

按热度按时间

l5tcr1uw1#

我修改了代码，SQL查询将数据按STUDENT分组，然后根据特定条件使用CASE语句和聚合函数（如MAX()）确定每个学生的最终SCORE和SCOREVAL。

SELECT 
    STUDENT, 
    CASE 
        WHEN MAX(SCORE = 'PASS') = 1 THEN 'PASS'
        WHEN MAX(SCORE = 'MOD1') = 1 AND MAX(SCORE = 'MOD2') = 1 THEN 'PASS'
        WHEN MAX(SCORE = 'MOD1') = 1 THEN 'MOD1'
        WHEN MAX(SCORE = 'MOD2') = 1 THEN 'MOD2'
        ELSE 'EXEMPT'
    END AS SCORE,
    CASE 
        WHEN MAX(SCORE = 'PASS') = 1 THEN MAX(CASE WHEN SCORE = 'PASS' THEN SCOREVAL END)
        WHEN MAX(SCORE = 'MOD1') = 1 AND MAX(SCORE = 'MOD2') = 1 THEN MAX(CASE WHEN SCORE = 'MOD1' THEN SCOREVAL END) + MAX(CASE WHEN SCORE = 'MOD2' THEN SCOREVAL END)
        WHEN MAX(SCORE = 'MOD1') = 1 THEN MAX(CASE WHEN SCORE = 'MOD1' THEN SCOREVAL END)
        WHEN MAX(SCORE = 'MOD2') = 1 THEN MAX(CASE WHEN SCORE = 'MOD2' THEN SCOREVAL END)
    END AS SCOREVAL
FROM TABLE1
GROUP BY STUDENT;

字符串

赞(0）回复(0）举报 12个月前

ukdjmx9f2#

假设您不希望在结果集的SCOREVAL列中出现NULL值，则可以合并一些规则并将其他规则构建到分数计算中。
例如，如果没有PASS条目，则可以无条件地对MOD1和MOD2条目的相对最大分数求和。将任何缺失值合并到0，以防止NULL值干扰另一个有效条目。

Schema（MySQL v8.0）

CREATE TABLE TABLE1 (
    STUDENT INT,
    SCORE TEXT,
    SCOREVAL INT
);

INSERT INTO TABLE1 VALUES
(1, 'PASS', 10),
(1, 'MOD1', 4),
(1, 'MOD2', 5),
(2, 'MOD1', 4),
(3, 'MOD2', 2),
(3, 'MOD2', 1),
(3, 'MOD1', 9),
(3, 'ER', 1),
(4, 'MOD2', 10),
(4, 'ER', 6),
(5, 'ER', 3),
(6, 'PASS', 8),
(6, 'PASS', 13),
(6, 'PASS', 7);

字符串

查询#1

SELECT 
    STUDENT, 
    CASE 
        WHEN MAX(SCORE = 'PASS') OR (MAX(SCORE = 'MOD1') AND MAX(SCORE = 'MOD2'))
            THEN 'PASS'
        WHEN MAX(SCORE = 'MOD1')
            THEN 'MOD1'
        WHEN MAX(SCORE = 'MOD2')
            THEN 'MOD2'
        ELSE
            'EXEMPT'
    END AS SCORE,
    CASE 
        WHEN MAX(SCORE = 'PASS')
            THEN MAX(CASE WHEN SCORE = 'PASS' THEN SCOREVAL END)
        ELSE
            COALESCE(MAX(CASE WHEN SCORE = 'MOD1' THEN SCOREVAL END),0)
            + COALESCE(MAX(CASE WHEN SCORE = 'MOD2' THEN SCOREVAL END),0)
    END AS SCOREVAL
FROM TABLE1
GROUP BY STUDENT;

型
| 学生|评分|评分|
| --|--|--|
| 1 |通过| 10 |
| 2 |Mod1| 4 |
| 3 |通过| 11 |
| 4 |MOD2| 10 |
| 5 |豁免| 0 |
| 6 |通过| 13 |
View on DB Fiddle

赞(0）回复(0）举报 12个月前

我来回答

mysql SQL如果按组

2条答案

相关问题

热门标签

最新问答