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

Question

我需要一些帮助，因为我是MYSQL的初学者，我试着查看其他帖子，因为这个错误没有得到任何进展。

您的SQL语法有错误；请检查与您的MySQL服务器版本对应的手册，以便在第7行使用接近“Select max(计数) from (选择活动，计数(*)从朋友组按活动分类”的正确语法

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

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(*)没有列别名。但它需要一个名称，因为它在子查询中。parentheses.
• The
• 子查询没有表别名。
• 外层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
                  );

Answer 2

sql字符串中有错误：

• 非别名计数(*)列
• No group 2表在不存在

的条件下

让我们尝试一下这个查询：

SELECT activity 
FROM ( SELECT activity, COUNT(*) AS Number FROM FRIENDS GROUP BY activity) AS getActivities
WHERE Number NOT IN (SELECT MIN(Number) FROM (SELECT activity, COUNT(*) AS Number FROM FRIENDS GROUP BY activity )  AS maximum)
AND Number NOT IN (SELECT MAX(Number) FROM (SELECT activity, COUNT(*) AS Number FROM FRIENDS GROUP BY activity) AS minimum)

Answer 3

如何修补。

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