SQL部分完全外部联接 [英] SQL Partial Full Outer Join

问题描述

这是我以前的在此处发布的延续.我有一张这样的桌子:

This is a continuation of my previous post here. I have a table like this:

Name    Id      Amount 
Name1   1       99
Name1   1       30
Name1   9       120.2
Name2   21      348
Name2   21      21
Name3   41      99

如果我运行此查询，则要感谢Juan Carlos Oropeza:

If I run this query, thanks to Juan Carlos Oropeza:

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
FROM 
       table1
GROUP BY [Name], [Id]

我得到这张桌子:

Name    Id      Count 
Name1   1       2
Name1   9       1
Name2   21      2
Name3   41      1

现在我有另一个这样的表:

Now I have another table like this:

Id      Return Amount
1       100
1       134.3
9       912.3
9       21
21      23.23
41      45

如果我运行此查询:

SELECT
    [Id],
    count([Return Amount]) as 'Returns'
FROM 
    table2
GROUP BY [Id]

我得到这张桌子:

Id      Returns 
1       2
9       2
21      1
41      1

我需要结合这两个表来创建一个像这样的表:

I need to combine these two tables to create a table like this:

Name    Id      Count      Returns
Name1   1       2          2
Name1   9       1          2
Name2   21      2          1
Name3   41      1          1

这是我的完全外部连接语句:

Here's my Full outer join statement:

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
FROM table1 AS A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
) B ON A.[Id] = B.[Id]
GROUP BY [Name], [Id]

但这给了我下表:

Name    Id      Count 
Name1   1       2
Name1   9       1
Name2   21      2
Name3   41      1

如何附加Returns列?我不确定在这种情况下要使用哪个联接，但是我受过良好教育的答案是完全外部联接.有什么想法吗?

How do I get the Returns column to attach? I'm not sure which join to use in this case but my best educated answer would be a full outer join. Any ideas?

推荐答案

您需要在SELECT语句中引用联接表.还有被引用列的GROUP BY.

You need to reference the joined table in your SELECT statement. And also GROUP BY that referenced column.

SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count',
       B."Returns"
FROM table1 AS A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
) B ON A.[Id] = B.[Id]
GROUP BY [Name], [Id], B."Return"

语义，但我认为最好的做法是在同一聚合级别上联接表.因此，我建议分别运行每个聚合表，然后再加入.这样可以防止意外的数据复制.像这样

Semantics, but I consider it best practice to JOIN tables on the same aggregate level. So I'd recommend running each aggregate table separately, then joining. This prevents accidental data-duplication. Like this

SELECT
    A.Name
    ,A.Id
    ,A."Count"
    ,B."Returns"
FROM
   (SELECT
       [Name],
       [Id],
       count([Amount]) as 'Count'
    FROM 
       table1
    GROUP BY [Name], [Id]
   ) A
FULL OUTER JOIN (
            SELECT
                [Id],
                count([Count]) as 'Returns'
            FROM 
                table2
            GROUP BY [Id]
    ) B ON A.[Id] = B.[Id]

这篇关于SQL部分完全外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！