代码之家 › 专栏 › 技术社区 › rborum

在子选择中添加带大小写的列

mysql

rborum · 技术社区 · 7 年前

我在一个更大的查询中有一个子选择,我试图使用CASE语句将一个计算字段添加到子选择中。

LEFT JOIN 
(
    SELECT Course_ID, Question_ID, Answer, (HIP =
        CASE
            WHEN Question_ID = 77 THEN "1"
            WHEN Question_ID = 78 THEN "2"
            WHEN Question_ID = 79 THEN "3"
            WHEN Question_ID = 80 THEN "4"
            WHEN Question_ID = 81 THEN "5"
            WHEN Question_ID = 82 THEN "6"
            ELSE "Undefined HIP"
        END)
    FROM proposals.new_gen_ed_answers
    WHERE Answer = 1
) ercehip
    ON course.Course_ID = ercehip.Course_ID

但是,当我尝试访问父select语句中的ercehip.HIP时,它会说它未定义。有什么办法解决这个问题吗?谢谢

2 回复 | 直到 7 年前

Madhur Bhaiya 7 年前

而不是使用 HIP = (..) Aliasing 相反。
还有,既然你的 CASE .. WHEN() 正在检查 Question_ID Case 子句,并使查询不那么冗长。

(
    SELECT Course_ID, Question_ID, Answer, 
        (CASE Question_ID
            WHEN 77 THEN "1"
            WHEN 78 THEN "2"
            WHEN 79 THEN "3"
            WHEN 80 THEN "4"
            WHEN 81 THEN "5"
            WHEN 82 THEN "6"
            ELSE "Undefined HIP"
        END) AS HIP 
    FROM proposals.new_gen_ed_answers
    WHERE Answer = 1
) AS ercehip

Maksym Fedorov 7 年前

必须根据MySQL语法要求确定计算字段的别名

SELECT Course_ID, Question_ID, Answer, (
    CASE
        WHEN Question_ID = 77 THEN "1"
        WHEN Question_ID = 78 THEN "2"
        WHEN Question_ID = 79 THEN "3"
        WHEN Question_ID = 80 THEN "4"
        WHEN Question_ID = 81 THEN "5"
        WHEN Question_ID = 82 THEN "6"
        ELSE "Undefined HIP"
    END) as HIP
FROM proposals.new_gen_ed_answers
WHERE Answer = 1

SELECT Course_ID, Question_ID, Answer, (
    CASE
        WHEN Question_ID = 77 THEN "1"
        WHEN Question_ID = 78 THEN "2"
        WHEN Question_ID = 79 THEN "3"
        WHEN Question_ID = 80 THEN "4"
        WHEN Question_ID = 81 THEN "5"
        WHEN Question_ID = 82 THEN "6"
        ELSE "Undefined HIP"
    END) HIP
FROM proposals.new_gen_ed_answers
WHERE Answer = 1

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前