php - MySQL 查询适用于 mysqlworkbench 但不适用于 php 环境

Question

我有一个查询可以在 MySQL 5.6 版本的 MySQL workbench 程序下完美运行，但是当我尝试在 PHP 环境中测试相同的查询时，出现以下错误:

{
  "code": 500,
  "response": {
    "error": {
      "description": "Query error",
      "error": {
        "errorInfo": [
          "42000",
          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 ')t1 HAVING price > 0 AND price < 804' at line 1"
        ]
      },
      "query": null
    }
  }
}

查询是这个:

SELECT * FROM(
   SELECT t.id, 
          t.user_id , 
          t.invoice_num, 
          t.registration, 
          SUM(t.price) as price
   FROM t_0 t 
   WHERE t.brandid=587
   GROUP BY t.invoice_num)t1 
   HAVING price > 0 AND price < 12
   ORDER BY id DESC LIMIT 100 OFFSET 0;

在 PHP 环境中，我只是使用 PDO 连接以这种方式执行查询:

$test_query = "here the query that I have";
$result = $con->query($test_query)->fetchAll(PDO::FETCH_ASSOC);

有人可以向我解释是什么导致了这个问题吗？在我看来，它的语法似乎非常正确。

Answer 1

尝试将查询更改为几乎适用于任何数据库的内容？
(几乎，因为 LIMIT 可以是特定于数据库的。F.e. MS SQL Server 使用 TOP 代替)

SELECT 
 MAX(t.id) AS id, 
 MAX(t.user_id) AS user_id, 
 t.invoice_num, 
 MAX(t.registration) AS registration, 
 SUM(t.price) as price
FROM t_0 t 
WHERE t.brandid = 587
GROUP BY t.invoice_num
HAVING SUM(t.price) BETWEEN 1 AND 11
ORDER BY id DESC 
LIMIT 100 OFFSET 0