我有这个Mysql查询,它有效:
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表中的所有条目,所以我试试这个:
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
我收到一个错误:
Unknown column ‘m.id’ in ‘where
clause’
为什么?
从MySql手册:
13.2.8.7. Correlated Subqueries
[...]
Scoping rule: MySQL evaluates from inside to outside.
所以……当子查询在SELECT部分时,这不起作用吗?我没有读到任何关于这一点.
有人知道吗?我该怎么办?我花了很长时间来构建这个查询……我知道它是一个怪物查询,但它在单个查询中得到了我想要的东西,而且我非常接近它的工作!
有人可以帮忙吗?