关于 php:SQLSTATE[42000] 聚合函数错误

SQLSTATE[42000] Error on aggregation functions

您好，我在我的 SQL 查询中遇到了一个错误，无法弄清楚是什么问题。这是迄今为止在 Barmar 的帮助下的查询。

1
2
3
4
5
6
7
8
9
10
11
12

$query =“SELECT c.*, count(s.curso_id) as count, SUM(IF(s.status = ‘aprobado’, 1, 0)) AS count_approved , SUM(IF(s.status = ‘cupolleno’, 1, 0)) AS count_cupolleno
, SUM(IF(s.status = ‘cancelado’, 1, 0)) AS count_cancelado, SUM(IF(s.status = ‘noacion’, 1, 0)) AS count_noacion, SUM(IF(s.status = ‘ama_de_casa’, 1, 0)) AS count_ama_de_casa
, SUM(IF(s.status = ‘cliente_externo’, 1, 0)) AS count_cliente_externo
FROM cursos_modulos AS c
LEFT JOIN subscriptions AS s ON s.curso_id = c.id
LEFT JOIN users AS u ON u.userID = s.user_id GROUP BY c.id WHERE 1″;
if (!empty($id)) { $query .=” AND c.id = ‘$id‘”; }
if (!empty($ciudad)) { $query .=” AND c.ciudad = ‘$ciudad‘”; }
if (!empty($tipo)) { $query .=” AND c.tipo = ‘$tipo‘”; }
if (!empty($titulo)) { $query .=” AND c.titulo = ‘$titulo‘”; }
if (!empty($status)) { $query .=” AND c.status = ‘$status‘”; }
$paginate = new pagination($page, $query, $options);

我得到的错误信息如下：

Fatal error: Uncaught exception ‘PDOException’ with message
‘SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ‘WHERE 1 AND
c.id = ‘1’ LIMIT 0, 30′ at line 6′ in
E:\\xampp\\htdocs\\admin\\class\\pagination.php:376 Stack trace: #0
E:\\xampp\\htdocs\\admin\\class\\pagination.php(376):
PDOStatement->execute() #1
E:\\xampp\\htdocs\\admin\\class\\pagination.php(202):
pagination->excecute_query() #2
E:\\xampp\\htdocs\\admin\\class\\pagination.php(162):
pagination->run(1, ‘SELECT c., cou…’, Array) #3
E:\\xampp\\htdocs\\admin\\search.php(146):
pagination->__construct(1, ‘SELECT c., cou…’, Array) #4 {main}
thrown in E:\\xampp\\htdocs\\admin\\class\\pagination.php on line
376

group by 子句应该在 where 子句之后。即：

1
2
3
4
5
6
7
8
9
10
11
12

$query =“SELECT c.*, count(s.curso_id) as count, SUM(IF(s.status = ‘aprobado’, 1, 0)) AS count_approved , SUM(IF(s.status = ‘cupolleno’, 1, 0)) AS count_cupolleno
, SUM(IF(s.status = ‘cancelado’, 1, 0)) AS count_cancelado, SUM(IF(s.status = ‘noacion’, 1, 0)) AS count_noacion, SUM(IF(s.status = ‘ama_de_casa’, 1, 0)) AS count_ama_de_casa
, SUM(IF(s.status = ‘cliente_externo’, 1, 0)) AS count_cliente_externo
FROM cursos_modulos AS c
LEFT JOIN subscriptions AS s ON s.curso_id = c.id
LEFT JOIN users AS u ON u.userID = s.user_id WHERE 1″;
if (!empty($id)) { $query .=” AND c.id = ‘$id‘”; }
if (!empty($ciudad)) { $query .=” AND c.ciudad = ‘$ciudad‘”; }
if (!empty($tipo)) { $query .=” AND c.tipo = ‘$tipo‘”; }
if (!empty($titulo)) { $query .=” AND c.titulo = ‘$titulo‘”; }
if (!empty($status)) { $query .=” AND c.status = ‘$status‘”; }
$query .=” GROUP BY c.id”;

相关讨论

这看起来是正确的解决方案，唯一的问题是它给了我一个错误；解析错误：语法错误，第 147 行 E:\\\\xampp\\\\htdocs\\\\admin\\\\search.php 中的意外 ‘$paginate’ (T_VARIABLE)
我在最后一个语句的末尾缺少一个 ; 。添加它应该可以解决问题。

where 1 能为你做什么？尝试杀死它。

以下不会引发 1064 错误：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

create table cursos_modulos
( id int not null

);

create table subscriptions
( curso_id int not null,
user_id int not null,
status varchar(100) not null
);

create table users
( userID int not null
);

SELECT c.id,
count(s.curso_id) as count,
SUM(IF(s.status = ‘aprobado’, 1, 0)) AS count_approved,
SUM(IF(s.status = ‘cupolleno’, 1, 0)) AS count_cupolleno,
SUM(IF(s.status = ‘cancelado’, 1, 0)) AS count_cancelado,
SUM(IF(s.status = ‘noacion’, 1, 0)) AS count_noacion,
SUM(IF(s.status = ‘ama_de_casa’, 1, 0)) AS count_ama_de_casa,
SUM(IF(s.status = ‘cliente_externo’, 1, 0)) AS count_cliente_externo
FROM cursos_modulos AS c
LEFT JOIN subscriptions AS s ON s.curso_id = c.id
LEFT JOIN users AS u ON u.userID = s.user_id
GROUP BY c.id

SQLSTATE[42000] Error on aggregation functions

猜你喜欢