SQL-同时使用group by和details查询

Question

我有如下sql查询：

SELECT sum(amount) AS sum, incomes_category_assigned_to_users.name AS category_name
        FROM incomes 
        INNER JOIN incomes_category_assigned_to_users on incomes.income_category_assigned_to_user_id = incomes_category_assigned_to_users.id 
        WHERE (incomes.user_id = :user_id AND date_of_income >= :first_date AND date_of_income <= :second_date)
        GROUP BY incomes.income_category_assigned_to_user_id 
        ORDER BY sum DESC;

结果是按类别分组的用户收入-显示为数组。

我想添加subquery-get这些收入，而不是分组的，这样用户就可以在array中展开字段并详细地看到它。

谢谢你的帮助

Answer 1

MySQL不支持数组。一种方法是将值放入字符串中：

SELECT SUM(amount) AS sum,
       GROUP_CONCAT(amount) as amounts,
       icau.name AS category_name
FROM incomes i JOIN
     incomes_category_assigned_to_users icau
     ON i.income_category_assigned_to_user_id = icau.id 
WHERE i.user_id = :user_id AND
      date_of_income >= :first_date AND
      date_of_income <= :second_date
GROUP BY icau.name
ORDER BY sum DESC;

请注意我对查询所做的更改：

• 表具有合理的表别名。它们使查询更易于写入和读取。
• 只使用和的where子句中不需要括号。
• GROUP BY键现在与未聚合的SELECT列一致。