时间戳

首页 > 解决方案 > 用不同的字符串替换 SQL 语句的输出

mysql - 用不同的字符串替换 SQL 语句的输出

问题描述

我目前有一个正在填充表格的输出。它给出的输出之一是学生表中的一年。每当输出 6 作为年份时，我需要它返回 L6，当输出 7 时，我需要 U6。我做了以下尝试：

"$stmt = $conn->prepare(
              "SELECT st.Name AS student, st.House AS house, T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
              CASE
                WHEN st.Year = 6 THEN 'L6'
                WHEN st.Year = 7 THEN 'U6'
                ELSE st.Year
              END CASE as year
              From Students AS st INNER JOIN Student_Choices AS sc
              ON st.Username = sc.Username INNER JOIN Current_DB AS db
              ON sc.DB_year = db.DB
              INNER JOIN Choices AS c1
              ON sc.T1_Choice = c1.Choice_ID
              INNER JOIN Sports AS T1
              ON c1.Sport_ID = T1.Sport_ID
              INNER JOIN Choices AS c2
              ON sc.T2_Choice = c2.Choice_ID
              INNER JOIN Sports AS T2
              ON c2.Sport_ID = T2.Sport_ID
              INNER JOIN Choices AS c3
              ON sc.T3_Choice = c3.Choice_ID
              INNER JOIN Sports AS T3
              ON c3.Sport_ID = T3.Sport_ID
              ");

然后我得到一个错误：

errorSQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；查看与您的 MariaDB 服务器版本相对应的手册，了解在“CASE WHEN st.Year = 6 THEN 'L6' WHEN st.”附近使用的正确语法。在第 2 行

任何解决方案都会非常感谢。

标签： mysqlsqlmariadbcase

解决方案

答案如下：

SELECT st.Name AS student, st.House AS house,
              (CASE WHEN st.Year = 6 THEN 'L6' WHEN st.Year = 7 THEN 'U6' ELSE st.Year END) as year,
              T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
              From Students AS st
              INNER JOIN Student_Choices AS sc
              ON st.Username = sc.Username INNER JOIN Current_DB AS db
              ON sc.DB_year = db.DB
              INNER JOIN Choices AS c1
              ON sc.T1_Choice = c1.Choice_ID
              INNER JOIN Sports AS T1
              ON c1.Sport_ID = T1.Sport_ID
              INNER JOIN Choices AS c2
              ON sc.T2_Choice = c2.Choice_ID
              INNER JOIN Sports AS T2
              ON c2.Sport_ID = T2.Sport_ID
              INNER JOIN Choices AS c3
              ON sc.T3_Choice = c3.Choice_ID
              INNER JOIN Sports AS T3
              ON c3.Sport_ID = T3.Sport_ID

CASE需要在括号中，并且选择时需要在之后的年份

推荐阅读