MySQL 带有 CASE WHEN、INNER JOIN 和 GROUP BY1 的多重 SELECT 查询

Question

我尝试使用内部联接和分组选项构建一个多重选择查询，但我不太明白为什么我的查询不起作用(我不熟悉 SQL 和 php，也许这就是原因)。我收到错误消息，指出我的语法错误。

我有多个具有相同结构的表，并且我希望在一个查询中将某些案例按一个公共(public)变量分组。

查询是这样的:

SELECT
    (SELECT Var1, 
    MAX( CASE WHEN Var2 ='2015' AND Var3='A1_U18' THEN Var4 ELSE 0 END ) AS 'A1_U18_0',
    MAX( CASE WHEN Var2 ='2015' AND Var3='A31_U6' THEN Var4 ELSE 0 END ) AS 'A31_U6_0'
    FROM table_a) AS a,

    (SELECT Var1,
    MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U3' THEN Var4 ELSE 0 END ) AS 'B21_U3_0',
    MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U6' THEN Var4 ELSE 0 END ) AS 'B21_U6_0'
    FROM table_b) AS b,

    (SELECT Var1,
    MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U3' THEN Var4 ELSE 0 END ) AS 'C21_U3_0',
    MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U6' THEN Var4 ELSE 0 END ) AS 'C21_U6_0'
    FROM table_c) AS c

INNER JOIN a b
    ON a.Var1 = b.Var1
INNER JOIN a c
    ON a.Var1 = c.Var1

GROUP BY a.Var1

我做错了什么？

Answer 1

1. 您的子查询无法返回多个字段
2. 您错过了包含FROM a
3. 要声明别名，您需要使用反引号“`”，您正在使用单引号 '，这适用于字符串。

我强烈建议您做一个基本的 mySql 教程， This 一个很有帮助

但是你可能需要这样的东西。

SELECT a.*, 
       b.`B21_U3_0`, b.`B21_U6_0`,
       c.`C21_U3_0`, c.`C21_U6_0`

FROM 
    (SELECT Var1, 
            MAX( CASE WHEN Var2 ='2015' AND Var3='A1_U18' 
                      THEN Var4 ELSE 0 END ) AS `A1_U18_0`,
            MAX( CASE WHEN Var2 ='2015' AND Var3='A31_U6' 
                      THEN Var4 ELSE 0 END ) AS `A31_U6_0`
     FROM table_a
     GROUP BY Var1
    ) AS a
JOIN    
    (SELECT Var1,
            MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U3' 
                      THEN Var4 ELSE 0 END ) AS `B21_U3_0`,
            MAX( CASE WHEN Var2 ='2015' AND Var3='B21_U6' 
                      THEN Var4 ELSE 0 END ) AS `B21_U6_0`
     FROM table_b
     GROUP BY Var1 
    ) AS b
  ON a.Var1 = b.Var1
JOIN   
    (SELECT Var1,
           MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U3' 
                     THEN Var4 ELSE 0 END ) AS `C21_U3_0`,
           MAX( CASE WHEN Var2 ='2015' AND Var3='C21_U6' 
                     THEN Var4 ELSE 0 END ) AS `C21_U6_0`
     FROM table_c
     GROUP BY Var1
    ) AS c
ON a.Var1 = c.Var1