MySql如何通过 `END AS`或另一个 `CASE`检查另一个条件来获取 `IF`语句的 `CASE`

Question

我有以下查询，我想做的是通过检查另一个条件来控制 SELECT CASE 的 END AS。

set @lang = 'pr';

select 
  COUNT(t1.id) as total, 
  COUNT(t1.id) as 'Total', 
  CASE t2.`precence`
        WHEN '0' THEN 'Absent'
        WHEN '1' THEN 'Present'
        ELSE 'Unknown'
    END AS series,
  CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS ticks,
  CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS abv,
    courses.no AS label,
  CASE t2.`precence`
        WHEN '0' THEN IF(@lang = 'en','Absent Participant',IF(@lang = 'pr','غیر حاضر',IF(@lang = 'pa','test','unkown')))
        WHEN '1' THEN IF(@lang = 'en','Present Participant',IF(@lang = 'pr','حاضر',IF(@lang = 'pa','حاضر','unkown')))
        ELSE IF(@lang = 'en','Unknown State',IF(@lang = 'pr','نامعلوم',IF(@lang = 'pa','نامعلوم','unkown')))
    END AS IF(@lang = 'en','Attendance Status',IF(@lang = 'pr','وضعیت حاضری',IF(@lang = 'pa','وضعیت حاضری','Attendance State')))),
    CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS IF(@lang = 'en','Attendance Date',IF(@lang = 'pr','تاریخ حاضری',IF(@lang = 'pa','تاریخ حاضری','Attendance Date'))),
    CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'Attendance Date (abv)',
    courses.no AS 'Course Number'

FROM course_attendances as t1 left join `student_attendances` as t2 on t2.`course_attendance_id` = t1.`id`
RIGHT JOIN courses ON courses.id = t1.course_id
WHERE t1.`course_id` = '1' AND t1.date BETWEEN '2015-01-01' AND '2016-11-17'
GROUP BY t1.`date`,t2.`precence`

但是在 CASE 语句的 END AS 部分查询停止并显示查询错误，我该如何解决。

Answer 1

SQL 不允许您使用 IF 语句控制“AS”子句的列名。在此上下文中跟在 AS 后面的词必须是一个简单的列名。

SQL 查询会生成列的列表，其名称和数据类型是在分析查询时定义的，而不是在运行查询时定义的。