使用COUNT聚合创建MySQL视图

Question

我正在创建一个带有GROUP BY子句的标准COUNT查询。

CREATE VIEW view1 AS
SELECT
t2.column3                  ,
t1.id AS t1_id              ,
t2.column4                  ,
COUNT ( t3.id ) AS t3_count , -- Error here
COUNT ( t4.id ) AS t4_count
FROM      table1 t1
LEFT JOIN table2 t2 ON t2.column1 = 25 AND
t2.column2 = t1.id
LEFT JOIN table3 t3 ON t3.column1 = t1.id
LEFT JOIN table4 t4 ON t4.column1 = t1.id
GROUP BY t2.column3 ,
t1.id      ,
t2.column4 ;

但这给出了一个错误：

Error Code: 1630. FUNCTION mydb.COUNT does not exist.

为什么MySQL认为我试图在数据库中调用用户定义的函数
它是否不将COUNT识别为内置聚合操作？

我也尝试过COUNT ( t3.* )，但它给出了一个通用语法错误
我不能使用COUNT ( * )，因为同时有多个LEFT JOINS在计数。

Answer 1

我会删除空格：

CREATE VIEW view1 AS
SELECT
t2.column3                  ,
t1.id AS t1_id              ,
t2.column4                  ,
COUNT(t3.id) AS t3_count , -- Error here
COUNT(t4.id) AS t4_count
FROM      table1 t1
LEFT JOIN table2 t2 ON t2.column1 = 25 AND
t2.column2 = t1.id
LEFT JOIN table3 t3 ON t3.column1 = t1.id
LEFT JOIN table4 t4 ON t4.column1 = t1.id
GROUP BY t2.column3 ,
t1.id      ,
t2.column4 ;

db<gt；小提琴演示

SELECT COUNT(*)
-- 1
SELECT COUNT (*)
-- You have an error in your SQL syntax; 
SELECT COUNT ( * )
-- You have an error in your SQL syntax; 

---

附录：

正如@Paul Spiegel评论的那样，这种行为可能会改变：

set session sql_mode = concat(@@sql_mode, ',IGNORE_SPACE');
SELECT COUNT ( * )
-- 1

db<gt；小提琴demo2