MySQL IF 语句语法错误

Question

已关闭。此问题需要 debugging details 。目前不接受答案。

---

编辑问题以包含 desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem 。这将有助于其他人回答问题。

已关闭 5 年前。

Improve this question

我尝试为每门类(class)获取空缺职位，因为每门类(class)的空缺职位有限；

所以需要返回:

类(class) 1 - 无空缺
类(class) 2 - 2 个空缺

SELECT
                        curs.CURSO_ID,
                        curs.CURSO_NOME,
                        curs.CURSO_DATA_INICIO,
                        curs.CURSO_DATA_FIM,
                        curs.CURSO_DESCRICAO,
                        curs.CURSO_LINK,
                        cate.CAT_CUR_ID,
                        cate.CAT_CUR_DESCRICAO,
                        curs.CURSO_VAGAS,

                        (
                            SELECT COUNT(sol.SOLI_ID)
                            FROM 
                            solicitacao AS sol
                            WHERE 
                            sol.SOLI_FK_CURSO_ID = curs.CURSO_ID 
                        ) AS pedido,

                    IF (
                         curs.CURSO_VAGAS > pedido THEN (curs.CURSO_VAGAS - pedido) ELSE 0
                       ) AS totalVagas


                     /** CASE WHEN (curs.CURSO_VAGAS > pedido) THEN (curs.CURSO_VAGAS - pedido) ELSE 0 END AS totalVagas **/

                      FROM 
                        curso AS curs

                      LEFT JOIN curso_categoria AS cate ON cate.CAT_CUR_ID = curs.CURSO_FK_CATEGORIA_ID

                      ORDER BY
                        CURSO_DATA_INICIO ASC

感谢帮助

Answer 1

试试这个 IF 语法:

 IF (curs.CURSO_VAGAS > (
    SELECT COUNT(sol.SOLI_ID) 
    FROM solicitacao AS sol
    WHERE 
    sol.SOLI_FK_CURSO_ID = curs.CURSO_ID
 ), (curs.CURSO_VAGAS - (
    SELECT COUNT(sol.SOLI_ID) 
    FROM solicitacao AS sol
    WHERE 
    sol.SOLI_FK_CURSO_ID = curs.CURSO_ID
 )), 0) AS totalVagas

您只能在 GROUP BY、ORDER BY 或 HAVING 子句中使用别名