MySQL 1064 错误 解决语法错误的方法是什么？

Question

我需要一些帮助，因为我是 MYSQL 的初学者，我尝试查看其他帖子来了解此错误，但没有得到任何结果。

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 7 行的“SELECT max(count) from (Select Activity, count(*) from FRIENDS group by Activity”附近使用的正确语法

为什么会生成此错误以及如何修复此错误？

SELECT activity
FROM (SELECT activity, count(*) FROM FRIENDS GROUP BY activity) AS getActivities
WHERE count NOT IN (
    SELECT min(count)
        FROM (SELECT activity, count(*) FROM FRIENDS GROUP BY activity ) AS maximum,
    SELECT max(count)
        FROM (SELECT activity, count(*) FROM FRIENDS GROUP BY activity) AS minimum
)

Answer 1

这是您的查询:

select activity
from (Select activity, count(*)
      from FRIENDS
      group by activity
     ) as getActivities
where count not in (Select min(count) from (Select activity, count(*) from FRIENDS group by activity ) as maximum,
                    SELECT max(count) from (Select activity, count(*) from FRIENDS group by activity) as minimum
                   );

您有多个错误。例如:

• count(*) 没有列别名。但它需要一个名称，因为它位于子查询中。
• 子查询没有表别名。
• 外部 where 中的子查询带有括号。
• , 表示您希望子查询作为标量子查询，但它们可以返回多行。

我认为你打算:

select f.activity
from friends f
group by f.activity
having count(*) > (select count(*)
                   from friends f2
                   group by f2.activity
                   order by count(*) asc
                   limit 1
                  ) and
       count(*) < (select count(*)
                   from friends f2
                   group by f2.activity
                   order by count(*) desc
                   limit 1
                  );