SQL 如何将两个查询（全部联合）合并为一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1997519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I combine two queries (union all) into one row?
提问by paxdiablo
I have a tricky situation in trying to get information from multiple queries into a single row.
我在尝试将来自多个查询的信息放入一行时遇到了棘手的情况。
Consider the following table:
考虑下表：
CpuUage:
Time time
Group char(10)
Subsys char(4)
Jobs int
Cpu int
holding the following data:
持有以下数据：
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) --
The markers on the right (e.g., A1) are the sections in the report below that each row is supposed to affect.
右边的标记（例如，A1）是报告中每行应该影响的部分。
I need a query that can return a single row for each user group but with one proviso. The values for Jobsand Cpuhave to go into different columns depending on the subsystem ID and I'm only interested in the SYS7and NORMsubsystem IDs.
我需要一个查询，它可以为每个用户组返回一行，但有一个限制条件。为价值观Jobs，并Cpu有进入取决于子系统ID不同列，我只感兴趣SYS7和NORM子系统的ID。
So, for example, we need the following (the A/B/1/2bits are a cross-reference back to the rows above):
因此，例如，我们需要以下内容（这些A/B/1/2位是对上述行的交叉引用）：
<------ 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
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:
我们的旧报告解决方案可以运行多个查询（使用union all），然后对行进行后处理以组合具有相同组名的行，以便：
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
Unfortunately, our new solution does not allow post-processing and it all has to be done in the SQL query.
不幸的是，我们的新解决方案不允许后处理，这一切都必须在 SQL 查询中完成。
Keeping in mind that there may be groups with SYS7rows, NORMrows, both or neither, what's the best way to achieve this?
请记住，可能存在包含SYS7行、NORM行、两者或两者都不包含的组，实现这一目标的最佳方法是什么？
I thought about sub-querying the table from within an outer select but that may have performance ramifications.
我想过从外部选择中对表进行子查询，但这可能会影响性能。
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 everyfield (I can't make the outer query just for NORMjobs since the presence of a group with only SYS7rows wouldn't be caught that way).
此外，这将是一个痛苦，因为我必须使外部查询包括 NORM 和 SYS7 子系统，然后为每个字段运行一个子查询（我不能只对NORM作业进行外部查询，因为只有一个组的存在SYS7行不会被那样捕获）。
Can you bods weave any of your left-middle-inner-offside-join magic to come up with a viable solution?
你能用你的左-中-内-越位-加入魔法来提出一个可行的解决方案吗？
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.
我更喜欢与供应商无关的解决方案，但是，如果您必须使用特定于供应商的解决方案，那么该平台是 DB2。尽管如此，其他平台至少可以让我知道要尝试什么，所以我很高兴看到它们。
回答by BlueRaja - Danny Pflughoeft
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
回答by Disillusioned
This is a pivot table query. (Search on that if you require further info.)
这是一个数据透视表查询。（如果您需要更多信息，请搜索它。）
The query structure you want is something along the lines of the following:
您想要的查询结构类似于以下内容：
SELECT groupname,
SUM(CASE WHEN subsys = 'NORM' THEN jobs ELSE 0 END) AS NormJobs,
SUM(CASE WHEN subsys = 'NORM' THEN cpu ELSE 0 END) AS NormCpu,
SUM(CASE WHEN subsys = 'SYS7' THEN jobs ELSE 0 END) AS Sys7Jobs,
SUM(CASE WHEN subsys = 'SYS7' THEN cpu ELSE 0 END) AS Sys7Cpu,
SUM(CASE WHEN subsys NOT IN ('NORM', 'SYS7') THEN jobs ELSE 0 END) AS OtherJobs,
SUM(CASE WHEN subsys NOT IN ('NORM', 'SYS7') THEN cpu ELSE 0 END) AS OtherCpu
FROM ???
GROUP BY groupname
回答by OMG Ponies
It's a typical pivot query - here's how you'd do it with CASE statements:
这是一个典型的数据透视查询 - 以下是您如何使用CASE 语句执行此操作：
SELECT t.group,
SUM(CASE
WHEN t.subsys = 'NORM' THEN t.jobs
ELSE NULL
END CASE) AS NormJobs,
SUM(CASE
WHEN t.subsys = 'NORM' THEN t.cpu
ELSE NULL
END CASE) AS NormCpu,
SUM(CASE
WHEN t.subsys = 'SYS7' THEN t.jobs
ELSE NULL
END CASE) AS Sys7Jobs,
SUM(CASE
WHEN t.subsys = 'SYS7' THEN t.cpu
ELSE NULL
END CASE) AS Sys7Cpu
FROM CPUUSAGE t
GROUP BY t.group
Unfortunately, DB2's CASE statements need to end with END CASE, when Oracle/SQL Server/MySQL/Postgres doesn't. Well, PLSQL supports END CASE...
不幸的是，DB2 的 CASE 语句需要以 结尾END CASE，而 Oracle/SQL Server/MySQL/Postgres 则不需要。好吧，PLSQL 支持END CASE...
There's also the PIVOT syntax, which is also supported on Oracle 11g, and SQL Server 2005+.
