如何将两个查询(全部联合)合并到一行中? [英] 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)是下面报告中每一行应该影响的部分.
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
很遗憾，我们的新解决方案不允许进行后处理，而这一切都必须在 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 子系统，然后为 every 字段运行子查询(我不能只进行外部查询对于 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
这篇关于如何将两个查询(全部联合)合并到一行中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
