MySQL 左连接子查询失败
以下查询在 MySQL 5.x 中运行良好
SELECT
m_area.id, m_area.cn_areaName, m_area.de_areaName,
m_area.en_areaName,m_area.jp_areaName,t_shop.count
FROM
m_area left join
(
select t_shop.areaID, count(areaID) AS count
from t_shop
group by t_shop.areaID
) t_shop
on m_area.id = t_shop.areaID
但是,当我必须在具有相同数据库结构和数据的 4.0.23 MySQL 数据库中运行它时,它只会返回以下消息:
1064 - 您的 SQL 语法有错误。检查与您的 MySQL 服务器版本相对应的手册,了解在 '[
附近使用的正确语法select t_shop.areaID, count(areaID) AS 计数 来自 t_s
我尝试了很多次,但仍然失败。 MySQL 4.x 中不允许左连接到子查询吗?那么这意味着我必须使用临时表来完成它?
提前致谢!
Following query runs well in MySQL 5.x
SELECT
m_area.id, m_area.cn_areaName, m_area.de_areaName,
m_area.en_areaName,m_area.jp_areaName,t_shop.count
FROM
m_area left join
(
select t_shop.areaID, count(areaID) AS count
from t_shop
group by t_shop.areaID
) t_shop
on m_area.id = t_shop.areaID
However, when I have to run it in a 4.0.23 MySQL DB with same DB structure and data it just return following message:
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 '[
select t_shop.areaID, count(areaID) AS count from t_s
I tried many times but still failed. Is left join to subquery not allowed in MySQL 4.x ? Then that mean I have to do it with a temp table?
Thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
MySQL 4.0 并没有很好地支持子查询:在 MySQL 4.1 中可以使用子查询(至少以某种真正有用的方式)——而 MySQL 4.0 确实很旧了,现在......
例如,请参阅 MySQL 手册的此页:12.2.8。子查询语法 (引用,强调我的):
Subqueries were quite not well supported with MySQL 4.0 : it became possible to use them (at least, in some real, useful way) with MySQL 4.1 -- and MySQL 4.0 is really old, now...
See for instance this page of the MySQL manual : 12.2.8. Subquery Syntax (quoting, emphasis mine) :
取出“, count(areaID) AS count”
子查询中的多个列弄乱了连接。
临时表应该可以正常工作......
玩得开心!
take out ", count(areaID) AS count"
The multiple columns in the subquery is messing up the join.
A temp table should work fine ....
Have fun!
我唯一能想到的是将表名添加到子查询中的areaID中,或者将保留字
count
重命名为cnt。Only thing I could think of is adding the tablename to your areaID in the subquery or renaming the reserved word
count
to cnt.