加入三桌然后分组 [英] Joining three table then group

问题描述

我要联接三个表，然后将一列与另一列的值相加.

I am going to join three tables then sum one of the column multiplied with an value from another.

SELECT t1.column, t2.column, SUM(t1.column * t2.column)
FROM table1 t1 
     INNER JOIN table2 t2 
         ON t1.id = t2.id 
     JOIN table3 t3 
         ON t2.id = t3.id 
GROUP BY t1.column, t2.column;

此查询可以满足我的要求，但是我不理解为什么 GROUP BY 可以正常工作吗?

This query does what I want, BUT I do not understand why the GROUP BY works?

如果我将列添加到选择中，还必须将列添加到分组依据吗?

If I add columns to the select must I also add columns to the group by?

推荐答案

您真的知道您在这里做什么吗?

Do you actually know what you are doing here?

SELECT t1.column, t2.column, SUM(t1.column * t2.column)
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
JOIN table3 t3 ON t2.id = t3.id
GROUP BY t1.column, t2.column;

该查询在至少两种方式下非常可疑:

The query is very suspicious in at least 2 ways:

• 未使用Table3 -除了要验证t3中是否存在t2中ID的记录.你想要那个吗?潜在的陷阱如果每个t2记录有多个T3记录，您将获得笛卡尔积，这是SUM列的意外乘法.

• Table3 is not used - except to verify that a record exists in t3 for the id in t2. Did you want that? Potential pitfall If there are multiple T3 records per t2 record, you will get a cartesian product, an unintended multiplication of the SUM column.

按t1.column，t2.column分组-这结合了(t1.column，t2.column)的所有唯一组合，并求和 t1.column * t2.column 的结果.这真的是您所追求的吗?

GROUP BY t1.column, t2.column - this combines all the unique combinations of (t1.column, t2.column), and sums the result of t1.column * t2.column across them. Is this really what you are after?

对于第2点，请考虑以下(源)数据:

For point 2, consider this (source) data:

t1.id, t1.column, t2.column, t1.column*t2.column
1      2          3          6
2      2          3          6
3      3          3          9
4      3          4          12

您最终获得了输出

t1.column, t2.column, SUM(t1.column*t2.column)
2          3          12
3          3          9
3          4          12

看到(2,3)合并了总和.

See that (2,3) has combined the sum.

如果我将列添加到选择中，我还必须将列添加到分组依据.

If I add columns to the select I also must add columns to the group by.

SELECT中的列(MySQL等某些DBMS除外)必须是集合(例如sum/avg/min/max)或GROUP BY子句中的列._{您还可以使用其他表达式，例如标量函数或常量值，而不是直接从表中获取.}

Columns in the SELECT (with the exception of some DBMS like MySQL) have to be either an aggregate (e.g. sum/avg/min/max) or a column in the GROUP BY clause. _{There are other expressions you can use like scalar functions or constant values not directly from the tables.}

如果实际上需要与表 related 相关的列中的更多列，则需要清楚地考虑为什么.例如如果按列1分组并平均列2，那么您打算对列3做些什么-它应该来自哪一行?

If you actually need more columns from the table related to the aggregates, you need to think clearly about why. e.g. If you are grouping by column1 and averaging column2, what are you tring to do with column3 - which row should it come from?

这篇关于加入三桌然后分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！