gpt4 book ai didi

sql - 具有相关子查询的 MySql 范围问题

转载 作者:可可西里 更新时间:2023-11-01 06:44:20 26 4
gpt4 key购买 nike

我有这个 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 部分时,这是否不起作用?我没有读到任何相关内容。

有人知道吗?我应该怎么办?我花了很长时间来构建这个查询...我知道这是一个巨大的查询,但它在单个查询中得到了我想要的东西,而且我非常接近让它工作!

有人能帮忙吗?

最佳答案

您只能关联一层深度。

使用:

   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 = ?

关于sql - 具有相关子查询的 MySql 范围问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2435644/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com