mysql - 连接表时出现 1064 错误

Question

SELECT School_info.district_id,count(School_info.no_of_class) ls 
from   School_info where     school_level IN('ls')  
JOIN
SELECT t.district_id,count(t.no_of_class) p from School_info as t     
where school_level IN('ps')
ON
( School_info.district_id = t.district_id)

我的错误在哪里，我没有找到它。当我运行上面的查询时，它显示为:-

MySQL 说:文档

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 'JOIN SELECT t.district_id,count(t.no_of_class) p from School_info as t where sc' at line 2

Answer 1

您的查询有很多语法错误，但无论如何它都可以简化为:

select 
    district_id
    , sum(school_level = 'ls') ls 
    , sum(school_level = 'ps') p
from school_info 
where school_level in ('ls','ps') 
group by district_id

sum(school_level = 'p')使用 MySQL 对 sum() 内的表达式进行 bool 计算的事实如果为 true，则返回 1。对所有 1 求和得到的结果与计算行数相同。

这将为您提供如下输出:

district_id ls p

哪里ls和p是计数。