总结多表父子关系的公共列 [英] Summing common columns of multi table parent-child relation

问题描述

我有四个以父子关系构建的表。一个表具有一个主键，其他三个表（否则彼此不相关）具有该主键作为外键。也就是说，表A具有一个称为客户ID的主键。表B，C和D还具有客户ID和其他一些信息。

I have four tables which are structured in a parent-child relation. One table has a primary key and the other three tables, otherwise unrelated to each other, have that primary key as a foreign key. That is, Table A has a primary key called customer ID. Tables B, C, and D also have customer ID and some other information.

我想要实现的是对表B，C和D的column2求和。我尝试过：

What I want to achieve is to sum column2 of tables B, C, and D. I have tried:

SELECT dbo.a.primary_key,
       SUM(dbo.b.column2) AS Expr1,
       SUM(dbo.c.column2) AS Expr2, 
       SUM(dbo.d.column2) AS Expr3
FROM   dbo.a 
INNER JOIN dbo.b ON dbo.a.primary_key = dbo.b.column1 
INNER JOIN dbo.c ON dbo.a.primary_key = dbo.c.column1  
INNER JOIN dbo.d ON dbo.a.primary_key = dbo.d.column1
GROUP BY dbo.a.primary_key

这不会返回任何内容。我还尝试了一个左外部联接，该联接返回空值。我还尝试了下面的代码，这是我真正想要实现的：

This returns nothing. I have also tried a left outer join which returns nulls. I have also tried it with the below line, which is what I really want to achieve:

SUM(dbo.b.column2) + SUM(dbo.c.column2) + SUM(dbo.d.column2) AS Expr1

这可能是由于设计不良，因为我可以将这三个表有效地合并到一个表中，但我相信这样会比较整洁。

This may be down to poor design, as I could effectively join these three tables into one table, but believe it is tidier like this.

一旦我解决了这个问题，我想将此扩展为表B，C和D的column3，以便它们具有一个为int的日期字段，并链接回日期表（E）。我想将表A，B，C和D同时连接到新创建的E，并按日期和a.primary_key分组。这可能吗，还是我需要重新考虑设计？

Once I have this problem solved, I would like to expand this as column3 of tables B, C, and D, so that they have a date field that is an int and links back to a date table (E). I would like to join tables A, B, C, and D to a newly created E at the same time, grouped by date and a.primary_key. Is this possible, or will I need to rethink the design?

推荐答案

如果需要，可以使用APPLY代替JOIN。选择单个列。

编辑：如果存在任何 NULL 值，此查询应为您提供 SUM ...

EDIT2 ：将 CROSS APPLY 更改为外部应用，因为如果其中一个SUMS不存在，则整个ROW将从结果中删除。

You can use APPLY instead of JOINs in case you just need to SELECT a single Column.
EDIT: If there are any NULL values this query should get you a SUM...
EDIT2: Changed the CROSS APPLY to OUTER APPLY, because in case one of the SUMS is non existent, the whole ROW will be dropped from the result.

SELECT a.primary_key,
    b.Sum AS Expr1,
    c.Sum AS Expr2, 
    d.Sum AS Expr3,
    ISNULL(b.Sum, 0) + ISNULL(c.Sum, 0) + ISNULL(d.Sum, 0) as [GrandTotal]
FROM dbo.Agency_Table a
    OUTER APPLY (SELECT SUM(ISNULL(b.column2, 0)) [Sum] FROM dbo.b WHERE a.primary_key = b.column1) as b
    OUTER APPLY (SELECT SUM(ISNULL(c.column2, 0)) [Sum] FROM dbo.c WHERE a.primary_key = c.column1) as c
    OUTER APPLY (SELECT SUM(ISNULL(d.column2, 0)) [Sum] FROM dbo.d WHERE a.primary_key = d.column1) as d

这篇关于总结多表父子关系的公共列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！