mysql - 如何加入两个表，但只包含右表中的每一行一次？

Question

我有一个包含一些表的数据库，我想加入这些表，但是虽然会有多个匹配项，但希望预算表行只出现在它们匹配的第一行旁边，以便我在组之后汇总预算通过不重复相同的预算。

SELECT GROUP_CONCAT(DISTINCT categories.categoryId), 
GROUP_CONCAT(budgets.budget) FROM transactions
    JOIN categories ON transactions.categoryId = categories.categoryId
    JOIN budgets ON transactions.categoryId = budgets.categoryId
    WHERE transactions.userId = 1 AND transactions.date BETWEEN DATE_ADD(CURDATE(), INTERVAL -30 DAY) AND CURDATE()
GROUP BY categories.displayName

本质上，我希望 GROUP_CONCAT(budgets.budget) 列返回 204、209、206；而不是 204、204、209、206

如果有任何帮助，我将使用 2 个查询作为解决方法：

SELECT SUM(transactions.amount) AS spend, categories.displayName, 
GROUP_CONCAT(DISTINCT categories.categoryId) AS id FROM transactions
JOIN categories ON transactions.categoryId = categories.categoryId
WHERE transactions.userId = ${req.params.id} AND transactions.date BETWEEN DATE_ADD(CURDATE(), INTERVAL -30 DAY) AND CURDATE()
GROUP BY categories.displayName

SELECT SUM(budgets.budget) AS budget, categories.displayName, GROUP_CONCAT(DISTINCT categories.categoryId) FROM budgets 
JOIN categories ON categories.categoryId = budgets.categoryId
WHERE budgets.userId = ${req.params.id}
GROUP BY categories.displayName


res1.forEach((group, key) => {
  res2.forEach((result) => {
    if (result.displayName === group.displayName) {
      res1[key].budget = result.budget;
    }
  });
});

标签： mysqlsql