相关子查询的MySQL范围界定问题 [英] MySql scoping problem with correlated subqueries
问题描述
我正在执行此Mysql查询,它的工作原理是:
I'm having this Mysql query, It works:
SELECT
nom
,prenom
,(SELECT GROUP_CONCAT(category_en) FROM
(SELECT DISTINCT category_en FROM categories c WHERE id IN
(SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = 37)
) cS
) categories
,(SELECT GROUP_CONCAT(area_en) FROM
(SELECT DISTINCT area_en FROM areas c WHERE id IN
(SELECT DISTINCT area_id FROM m3allems_to_areas m2a WHERE m3allem_id = 37)
) aSq
) areas
FROM m3allems m
WHERE m.id = 37
结果是:
nom prenom categories areas
Man Multi Carpentry,Paint,Walls Beirut,Baalbak,Saida
它工作正确,但仅当我将所需的ID硬编码到查询中时(37). 我希望它适用于m3allem表中的所有条目,所以我尝试这样做:
It works correclty, but only when i hardcode into the query the id that I want (37). I want it to work for all entries in the m3allem table, so I try this:
SELECT
nom
,prenom
,(SELECT GROUP_CONCAT(category_en) FROM
(SELECT DISTINCT category_en FROM categories c WHERE id IN
(SELECT DISTINCT category_id FROM m3allems_to_categories m2c WHERE m3allem_id = m.id)
) cS
) categories
,(SELECT GROUP_CONCAT(area_en) FROM
(SELECT DISTINCT area_en FROM areas c WHERE id IN
(SELECT DISTINCT area_id FROM m3allems_to_areas m2a WHERE m3allem_id = m.id)
) aSq
) areas
FROM m3allems m
我得到一个错误:
"where"中的未知列"m.id" 条款"
Unknown column 'm.id' in 'where clause'
为什么? 从MySql手册中:
Why? From the MySql manual:
13.2.8.7. Correlated Subqueries
[...]
Scoping rule: MySQL evaluates from inside to outside.
那么...当子查询位于SELECT部分中时,这不起作用吗?我什么都没读.
So... do this not work when the subquery is in a SELECT section? I did not read anything about that.
有人知道吗?我该怎么办?我花了很长时间来构建此查询...我知道这是一个庞然大物的查询,但是它在单个查询中就可以满足我的要求,而且我已经接近使它开始工作了!
Does anyone know? What should I do? It took me a long time to build this query... I know it's a monster query but it gets what I want in a single query, and I am so close to getting it to work!
任何人都可以帮忙吗?
推荐答案
您只能将一级关联.
使用:
SELECT m.nom,
m.prenom,
x.categories,
y.areas
FROM m3allens m
LEFT JOIN (SELECT m2c.m3allem_id,
GROUP_CONCAT(DISTINCT c.category_en) AS categories
FROM CATEGORIES c
JOIN m3allems_to_categories m2c ON m2c.category_id = c.id
GROUP BY m2c.m3allem_id) x ON x.m3allem_id = m.id
LEFT JOIN (SELECT m2a.m3allem_id,
GROUP_CONCAT(DISTINCT a.area_en) AS areas
FROM AREAS a
JOIN m3allems_to_areas m2a ON m2a.area_id = a.id
GROUP BY m2a.m3allem_id) y ON y.m3allem_id = m.id
WHERE m.id = ?
这篇关于相关子查询的MySQL范围界定问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!