mysql - FROM 子句中的子查询不起作用

Question

使用 MySQL 5.7.14，以下查询在 5.6.17 版本中不起作用:

SELECT prep.Mbr_Id AS Mbr, 
       prep.Pra_Id AS Pra, 
       SUM( prep.MPra_VAL ) AS Val 
FROM    
    (
      SELECT membres.Mbr_Id AS Mbr_Id, 
             pratiques.Pra_Id AS Pra_Id, 
             0 AS MPra_VAL 
      FROM membres 
      JOIN pratiques 
      WHERE pratiques.Pra_Index = 1 
              UNION ALL 
      SELECT membres_pratiques.MPra_Mbr_Id AS Mbr_Id, 
             membres_pratiques.MPra_Pra_Id AS Pra_Id, 
             membres_pratiques.MPra_Val AS MPra_VAL 
      FROM membres_pratiques 
    ) AS prep
GROUP BY prep.Mbr_Id, 
         prep.Pra_Id 

在 SQL 选项卡中的 phpMyAdmin (5.6.25) 中编写此查询，出现以下错误:

. on "(" line, after the first FROM clause : 
    . an expression was expected ( close to ( )
    . unexpected token ( close to ) )
. on SELECT line after the ")" : 
    . this type of clause has already been analysed ( close to ) )

有没有人知道可能是什么问题？

Answer 1

尝试更简单的查询形式:

SELECT m.Mbr_Id, p.Pra_Id, mp.MPra_VAL 
FROM membres m CROSS JOIN
     pratiques p LEFT JOIN
     membres_pratiques mp
     ON mp.MPra_Mbr_Id = m.Mbr_Id AND mp.MPra_Pra_Id = p.Pra_Id
WHERE p.Pra_Index = 1 ;

只有在 membres_pratiques 中有多行“membres”和“pratiques”对时才需要聚合。