将两个表合并为一个输出 [英] Combine two tables for one output

问题描述

假设我有两张桌子:

已知时间:

<前>ChargeNum CategoryID 月 小时111111 1 2/1/09 10111111 1 3/1/09 30111111 1 4/1/09 50222222 1 3/1/09 40111111 2 4/1/09 50

未知时间:

<前>ChargeNum 月时数111111 2/1/09 70111111 3/1/09 40.5222222 7/1/09 25.5

我需要将这些小时(忽略月份)分组到一个数据表中，以便我的预期结果如下:

<前>ChargeNum CategoryID 小时111111 1 90111111 2 50111111 未知 110.5222222 1 40222222 未知 25.5

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

我需要对每个 ChargeNum/Category 组合的小时数求和.我更新了示例数据以反映这一点.

解决方案

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

SELECT ChargeNum, CategoryID, SUM(Hours)从已知时间GROUP BY ChargeNum, CategoryID联合所有SELECT ChargeNum, 'Unknown' AS CategoryID, SUM(Hours)来自未知时间按 ChargeNum 分组

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

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:

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.

解决方案

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

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 ALL as in above, it's no slower than running the two queries separately as it does no duplicate-checking.

这篇关于将两个表合并为一个输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！