如何将两个查询（union all）组合成一行？ [英] How do I combine two queries (union all) into one row?
问题描述
我尝试从多个查询中获取信息到一行，我有一个棘手的情况。
I have a tricky situation in trying to get information from multiple queries into a single row.
考虑下表：
CpuUage:
Time time
Group char(10)
Subsys char(4)
Jobs int
Cpu int
包含以下数据：
Time Group Subsys Jobs Cpu
----- ------ ------ ---- ---
00:00 group1 NORM 1 101 (grp1-norm) A1
01:00 group1 SYS7 3 102 (grp1-sys7) A2
01:00 group1 NORM 5 104 (grp1-norm) A1
02:00 group1 NORM 7 108 (grp1-norm) A1
02:00 group2 NORM 9 116 (grp2-norm) B1
02:00 group3 SYS7 11 132 (grp3-sys7) C2
03:00 group1 SYS7 13 164 (grp1-sys7) A2
03:00 group1 IGNR 99 228 (grp1-ignr) --
右侧的标记（例如 A1 ）是报表中的部分belo w /每行应该会影响。
The markers on the right (e.g., A1) are the sections in the report below that each row is supposed to affect.
我需要一个查询，可以为每个用户组返回一行，但有一个附带条件。 Jobs 和 Cpu 的值必须根据子系统ID进入不同的列，我只对 SYS7 和 NORM 子系统ID。
I need a query that can return a single row for each user group but with one proviso. The values for Jobs and Cpu have to go into different columns depending on the subsystem ID and I'm only interested in the SYS7 and NORM subsystem IDs.
所以，例如，我们需要以下内容（ A / B / 1/2 位是上面列的交叉引用）：
So, for example, we need the following (the A/B/1/2 bits are a cross-reference back to the rows above):
<------ 1 ------> <------ 2 ------>
Group NormJobs NormCpu Sys7Jobs Sys7Cpu
------ -------- ------- -------- -------
A: group1 13 313 16 266
B: group2 9 116 0 0
C: group3 0 0 11 164
我们的旧报告解决方案可以运行多个查询（使用 union all ），然后对这些行进行后处理相同的组名，以便：
Our old reporting solution could run multiple queries (with a union all), then post-process the rows to combine those with the same group name, so that:
Group NormJobs NormCpu Sys7Jobs Sys7Cpu
------ -------- ------- -------- -------
group1 13 313 0 0
group1 0 0 16 266
合并在一起，沿着以下行：
were merged together, along the lines of:
select groupname, sum(jobs), sum(cpu), 0, 0 from tbl
where subsys = 'NORM'
group by groupname
union all
select groupname, 0, 0, sum(jobs), sum(cpu) from tbl
where subsys = 'SYS7'
group by groupname
不幸的是，我们的新解决方案不允许post-处理，并且都必须在SQL查询中完成。
Unfortunately, our new solution does not allow post-processing and it all has to be done in the SQL query.
请记住可能会有 SYS7 行， NORM 行，两者都不是，最好的方式是什么？
Keeping in mind that there may be groups with SYS7 rows, NORM rows, both or neither, what's the best way to achieve this?
我想到了 - 从外部选择中查询表，但可能会有性能影响。
I thought about sub-querying the table from within an outer select but that may have performance ramifications.
此外，这将是一个痛苦，因为我必须使外部查询包括NORM和SYS7子系统然后为每个字段运行一个子查询（我不能使外部查询仅用于 NORM 作业，因为存在组只有 SYS7 行将不会被捕获）。
In addition, that would be a pain since I'd have to make the outer query include NORM and SYS7 subsystems then run a subquery for every field (I can't make the outer query just for NORM jobs since the presence of a group with only SYS7 rows wouldn't be caught that way).
你可以编织任何你的左中间 - 越位 - 加入魔法来提出可行的解决方案？
Can you bods weave any of your left-middle-inner-offside-join magic to come up with a viable solution?
我更喜欢与供应商无关的解决方案，但如果你你必须去供应商特定的，平台是DB2。不过，其他平台至少可以给我一个想法，所以我很高兴看到他们。
I'd prefer a vendor-agnostic solution but, if you must go vendor-specific, the platform is DB2. Still, other platforms may at least give me an ideas of what to try so I'm happy to see them.
推荐答案
我不明白子查询的问题，似乎应该是一样快：
I don't understand the problem with sub-querying, it seems like it should be just as fast:
select
sub.gn as groupname,
sum(sub.nj) as NormJobs, sum(sun.nc) as NormCpu,
sum(sub.sj) as Sys7Jobs, sum(sub.sc) as Sys7Cpu
from (
select
groupname as gn,
sum(jobs) as nj, sum(cpu) as nc,
0 as sj, 0 as sc
from tbl
where subsys = 'NORM'
group by groupname
union all select
groupname as gn,
0 as nj, 0 as nc,
sum(jobs) as sj, sum(cpu) as sc
from tbl
where subsys = 'SYS7'
group by groupname
) as sub
group by sub.gn
order by 1
这篇关于如何将两个查询（union all）组合成一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
