mysql - 我可以在 MySQL 中使用多个大小写吗？

Question

要求:

原始表:SCORE

+--------+----------+-----------+
| Math   | English  | Chemistry |
+--------+----------+-----------+
| 69     | 82       | 95        |
+--------+----------+-----------+

排序:- [0-80] -> C- [80-90] -> B- [90-100] ->A

输出应该是

+--------+----------+-----------+
| Math   | English  | Chemistry |
+--------+----------+-----------+
| C      | B        |    A      |
+--------+----------+-----------+

我应该在Oracle中进行测试，但现在我首先在MySQL上进行测试。我的代码如下:

CREATE table grade1(
Math varchar(10),
English varchar(10),
Chemistry varchar(10))

INSERT INTO grade1 (Math, English, Chemistry)
VALUES (

SELECT
CASE
when Math < 80 then 'C'
when Math > 80 and Math <90 then 'B'
when Math > 90 and Math <100 then 'A'
END
FROM SCORE,

SELECT
CASE
when English < 80 then 'C'
when English > 80 and English < 90 then 'B'
when English > 90 and English < 100 then 'A'
END
FROM SCORE,

SELECT
CASE
when Chemistry < 80 then 'C'
when Chemistry > 80 and Chemistry <90 then 'B'
when Chemistry > 90 and Chemistry <100 then 'A'
END  
FROM SCORE)

请检查是否有问题？因为我无法运行它。

Answer 1

这个怎么样？

INSERT INTO grade1 (Math, English, Chemistry)
    VALUES (
        SELECT
            CASE
                WHEN Math >= 90 then 'A'
                WHEN Math >= 80 then 'B'
                WHEN Math < 80 then 'C'
            END Math,
            CASE
                WHEN English >= 90 then 'A'
                WHEN English >= 80 then 'B'
                WHEN English < 80 then 'C'
            END English,
            CASE
                WHEN Chemistry >= 90 then 'A'
                WHEN Chemistry >= 80 then 'B'
                WHEN Chemistry < 80 then 'C'
            END Chemistry
        FROM SCORE)

您不必在每个 CASE 之前使用 SELECT。我更改了 WHEN 值的顺序。