mysql - 嵌套 SQL 查询在 MySql 中以不同形式运行时抛出错误

Question

我有一个 SELECT 查询，它应该有多个来自同一个表的嵌套 SELECT 查询，但不同的 WHERE 子句可以生成新列。

问题是每个子查询以其单一形式运行良好，但是当它们变成子查询时，MySql 抛出错误。

这是我的 SQL:

SELECT user, 
(SELECT SUM(amount) from my_table WHERE type='form1' group by user) as form1,
(SELECT SUM(amount) from my_table WHERE type='form2' group by user) as form2,
(SELECT SUM(amount) from my_table WHERE type='form3' group by user) as form3,
(SELECT SUM(amount) from my_table WHERE type='form4' group by user) as form4,
(SELECT SUM(amount) from my_table WHERE type='form5' group by user) as form5,
(SELECT SUM(amount) from my_table WHERE type='form6' group by user) as form6
from my_table group by user;

我希望查询产生这个结构:

user | form1 | form2 | form3 | form4 | form5 | form6
     |       |       |       |       |       |

我应该如何编辑此 SQL？

Answer 1

你想要条件聚合:

select user,
       sum(case when type = 'form1' then amount end) as form1,
       sum(case when type = 'form2' then amount end) as form2,
       sum(case when type = 'form3' then amount end) as form3,
       sum(case when type = 'form4' then amount end) as form4,
       sum(case when type = 'form5' then amount end) as form5,
       sum(case when type = 'form6' then amount end) as form6
from my_table
group by user;

您的错误至少是因为子查询返回了不止一行。子查询中的 group by 几乎意味着它们将为每个用户返回不止一行。