Mysql用法“as”

Question

我是一个web开发人员，我曾经面对过这个问题。。。我正在开发统计网页应用程序部分使用php和MySQL。

SELECT
  IF (cat1 = "S4", 1, 0) AS ncat1,
  IF (cat2 = "S4", 1, 0) AS ncat2,
  IF (cat3 = "S4", 1, 0) AS ncat3,
  IF (cat4 = "S4", 1, 0) AS ncat4,
  (ncat1 + ncat2 + ncat3 + ncat4) AS ntotal
FROM
  users

我想像这样得到ntotal格式。但是它不工作，未知列ncat1所以，我想fullstack开发人员可以帮助我更容易和快速的帮助我请。。。。谢谢。。。

Answer 1

您的ncat1和其他列名不在(ncat1+ncat2+ncat3+ncat4)作为ntotal行的范围内。所以你不能在那里指代他们。

尝试使用子查询将它们纳入范围，如下所示:

SELECT *, (ncat1 + ncat2 + ncat3 + ncat4) AS ntotal
  FROM (
      SELECT
         IF (cat1 = "S4", 1, 0) AS ncat1,
         IF (cat2 = "S4", 1, 0) AS ncat2,
         IF (cat3 = "S4", 1, 0) AS ncat3,
         IF (cat4 = "S4", 1, 0) AS ncat4
     FROM coursered
      ) subquery

Answer 2

您的别名（即NCAT1)不能在SQL语句中使用。

我将使用一个简单的子查询来解决这个问题:

SELECT  ncat1, ncat2, ncat3, ncat4,
        (ncat1 + ncat2 + ncat3 + ncat4) AS ntotal
  FROM  (
        SELECT
          IF (cat1 = "S4", 1, 0) AS ncat1,
          IF (cat2 = "S4", 1, 0) AS ncat2,
          IF (cat3 = "S4", 1, 0) AS ncat3,
          IF (cat4 = "S4", 1, 0) AS ncat4
        FROM
          users
        ) a