sql - 在与另一个表合并之前按其中一个参数对一个表进行分组

Question

我已经成功合并了 3 个表(使用 ID)，但是当我尝试添加第 4 个表时 - 输出完全困惑(值变得不切实际/错误)所以我认为我与这个事实有关第 4 个表有未分组的 ID，所以我需要在加入这个新表之前对它们进行分组。现在查询如下:

SELECT name, SUM(money) AS MONEY
FROM transactions
JOIN results ON transactions.id = results.id
JOIN more ON results.per_id = more.per_id
GROUP BY name
HAVING SUM(money)>500

当我加入新表时:

SELECT name, SUM(money) AS MONEY, SUM(data_from_NT1), SUM(data_from_NT2)
FROM transactions
JOIN results ON transactions.id = results.id
JOIN more ON results.per_id = more.per_id

JOIN newtable ON results.per_id = newtable.per_id

GROUP BY name
HAVING SUM(money)>500

是否可以执行命令 GROUP BY per_id:

(JOIN newtable ON results.per_id = newtable.per_id GROUP BY per_id)

在将这个新表添加到主表之前？上面的行不起作用。

Answer 1

是的，这是可能的，但你必须把它写成一个 SELECT。这是可能答案的第一个版本:

SELECT name,
       SUM(money)             AS money,
       SUM(nt2.data_from_NT1) AS data_from_NT1,
       SUM(nt2.data_from_NT2) AS data_from_NT2
  FROM transactions AS t
  JOIN results      AS r ON t.id = r.id
  JOIN more         AS m ON r.per_id = m.per_id
  JOIN (SELECT per_id, SUM(data_from_NT1) AS data_from_NT1, SUM(data_from_NT2) AS data_from_NT2
          FROM newtable GROUP BY per_id
       ) AS nt2 ON results.per_id = nt2.per_id
 GROUP BY name
HAVING SUM(money) > 500;

目前还不完全清楚您是否需要主选择列表中的“SUM of SUM”；你可能不知道。 name 或 money 列的来源也不清楚；我通常也会在它们前面加上适当的表别名。有了这些警告，这可能更接近您所追求的:

SELECT name, SUM(money) AS MONEY, nt2.data_from_NT1, nt2.data_from_NT2
  FROM transactions AS t
  JOIN results      AS r ON t.id = r.id
  JOIN more         AS m ON r.per_id = m.per_id
  JOIN (SELECT per_id, SUM(data_from_NT1) AS data_from_NT1, SUM(data_from_NT2) AS data_from_NT2
          FROM newtable GROUP BY per_id
       ) AS nt2 ON results.per_id = nt2.per_id
 GROUP BY name, nt2.data_from_NT1, nt2.data_from_NT2
HAVING SUM(money) > 500;

毫无疑问还有其他的写法。主查询中的分组可能更好地放入并行子查询中，在主查询中留下简单的直接连接。但我们没有实际为您执行此操作的信息。