MySQL查询在本地成功运行，但在服务器上出现DATE_FORMAT错误

Question

我有这个MySQL / MariaDB查询：

SELECT
   a.id as agent_id
 , a.full_name
 , a.email
 , a.phone
 , a.location
 , COUNT(cs.agent_id) AS tot_subscribers
 , (SELECT
      IFNULL(SUM(COUNT(cs.id)), 0) 
    WHERE
      DATE_FORMAT(cs.transaction_id, '%c') = 3
   ) AS month_subscribers
 FROM
   agents a
 LEFT JOIN
   cream_sales cs
 ON
   a.id = cs.agent_id
 GROUP BY
   a.id 

它在我的本地主机上成功运行，但在服务器上运行时出现以下DATE_FORMAT错误：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE DATE_FORMAT(cs.transaction_id, '%c') = 3) AS month_subscribers FROM agents' at line 1

本地DBMS详细信息：

Server type: MySQL
Server version: 5.7.21-0ubuntu0.16.04.1 - (Ubuntu)
Protocol version: 10
Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $

服务器DBMS详细信息：

Server type: MariaDB
Server version: 10.2.13-MariaDB - MariaDB Server
Protocol version: 10 
cpsrvd 11.68.0.33
Database client version: libmysql - 5.1.73
PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation 

谁能帮我找出导致这个问题的原因。提前谢谢。

Answer 1

我会使用EXTRACT(MONTH FROM ... )/MONTH，而不是依赖格式化数据：

SELECT a.id as agent_id,
      a.full_name,
      a.email, a.phone, a.location, COUNT(cs.agent_id) AS tot_subscribers,
     (SELECT IFNULL(SUM(COUNT(cs.id)), 0)
      WHERE  MONTH(cs.transaction_id) = 3) AS month_subscribers 
FROM agents a 
LEFT JOIN cream_sales cs 
  ON a.id = cs.agent_id 
 GROUP BY a.id;

编辑：

不需要子查询。条件聚合将执行相同的操作：

SELECT a.id as agent_id,
      a.full_name,
      a.email, a.phone, a.location, COUNT(cs.agent_id) AS tot_subscribers,
      SUM(COUNT(CASE WHEN MONTH(cs.transaction_id) = 3 
                     THEN cs.id END)) AS month_subscribers 
FROM agents a 
LEFT JOIN cream_sales cs 
  ON a.id = cs.agent_id 
 GROUP BY a.id, a.full_name, a.email, a.phone, a.location;

Answer 2

SUM(COUNT(...))没有任何意义。不要嵌套聚合函数。

也许这就是您在这一列中所需要的：

( SELECT COUNT(id) FROM cs
      WHERE MONTH(transaction_id) = 3
) AS month_subscribers

备注：

• COUNT(x)检查x是否不是null.
• MONTH()更简单(但效率也不会更高)。