SQL 将两个表合并为一个输出

Combine two tables for one output

提问by Matthew Jones

Say I have two tables:

假设我有两个表：

KnownHours:

已知时间：

ChargeNum    CategoryID    Month    Hours
111111       1             2/1/09   10
111111       1             3/1/09   30
111111       1             4/1/09   50
222222       1             3/1/09   40
111111       2             4/1/09   50

UnknownHours:

未知时间：

ChargeNum   Month   Hours
111111      2/1/09  70
111111      3/1/09  40.5
222222      7/1/09  25.5

I need to group these hours, ignoring Month, into a single data table so that my expected result is the following:

我需要将这些小时（忽略 Month）分组到一个数据表中，以便我的预期结果如下：

ChargeNum    CategoryID     Hours
111111       1              90
111111       2              50
111111       Unknown        110.5
222222       1              40
222222       Unknown        25.5

I cannot seem to figure this out. Any help would be greatly appreciated!

我似乎无法弄清楚这一点。任何帮助将不胜感激！

EDIT: I need to sum the hours for each ChargeNum/Category combination. I updated the sample data to reflect this.

编辑：我需要总结每个 ChargeNum/Category 组合的小时数。我更新了示例数据以反映这一点。

回答by lc.

You'll need to use UNIONto combine the results of two queries. In your case:

您需要使用UNION来组合两个查询的结果。在你的情况下：

SELECT ChargeNum, CategoryID, SUM(Hours)
FROM KnownHours
GROUP BY ChargeNum, CategoryID
UNION ALL
SELECT ChargeNum, 'Unknown' AS CategoryID, SUM(Hours)
FROM UnknownHours
GROUP BY ChargeNum

Note - If you use UNION ALLas in above, it's no slower than running the two queries separately as it does no duplicate-checking.

注意 - 如果您UNION ALL像上面那样使用，它并不比单独运行两个查询慢，因为它不进行重复检查。

回答by BenAlabaster

In your expected output, you've got the second last row sum incorrect, it should be 40 according to the data in your tables, but here is the query:

在您的预期输出中，您的倒数第二行总和不正确，根据表中的数据，它应该是 40，但这里是查询：

Select  ChargeNum, CategoryId, Sum(Hours)
From    (
    Select  ChargeNum, CategoryId, Hours
    From    KnownHours
    Union
    Select  ChargeNum, 'Unknown' As CategoryId, Hours
    From    UnknownHours
) As a
Group By ChargeNum, CategoryId
Order By ChargeNum, CategoryId

And here is the output:

这是输出：

ChargeNum  CategoryId 
---------- ---------- ----------------------
111111     1          40
111111     2          50
111111     Unknown    70
222222     1          40
222222     Unknown    25.5

回答by AccessProgrammer

Can we take this a step farther and say I only want to see rows when combined that have 50 Hours or more... I've tried this, but get an error that it can't find SumHours...

我们可以更进一步说我只想看到组合时有 50 小时或更多的行......我试过这个，但得到一个错误，它找不到 SumHours ......

Select  ChargeNum, CategoryId, Sum(Hours) As SumHours
From    (
    Select  ChargeNum, CategoryId, Hours
    From    KnownHours
    Union
    Select  ChargeNum, 'Unknown' As CategoryId, Hours
    From    UnknownHours
) As a
WHERE (SumHours>=50)
Group By ChargeNum, CategoryId
Order By ChargeNum, CategoryId

So I tried

所以我试过了

    Select  ChargeNum, CategoryId, Sum(Hours) As SumHours
From    (
    Select  ChargeNum, CategoryId, Hours
    From    KnownHours
    Union
    Select  ChargeNum, 'Unknown' As CategoryId, Hours
    From    UnknownHours
) As a
WHERE (Hours>=50)
Group By ChargeNum, CategoryId
Order By ChargeNum, CategoryId

But this isn't giving the sum of the hours on both tables...

但这并没有给出两张桌子上的小时数总和......