问题描述
我有两个表,每个表都将自己的结果集作为一行生成.我想将这些结果集合并为一行.例如:
I have two tables, each which produce their own result-sets as a single row. I would like to join these result-sets into one single row. For example:
SELECT * FROM Table 1 WHERE Year = 2012 AND Quarter = 1
结果:
Year Quarter Name State Mail 2012 1 Bob NY bob@gmail
查询 #2:
SELECT * FROM Table 2 WHERE Year = 2012 AND Quarter = 1
结果:
Year Quarter Name State Mail 2012 1 Greg DC greg@gmail
期望的结果集:
SELECT * FROM Table 3 WHERE Year = 2012 AND Quarter = 1 Year Quarter T1Name T1State T1Mail T2Name T2State T2Mail 2012 1 Bob NY bob@gmail Greg DC greg@gmail
结果被加入/旋转到 Year 和 Quarter 的组合上,这将通过参数输入到查询中.任何帮助将不胜感激.提前致谢!
The results are joined/pivoted onto the combination of Year and Quarter, which will be fed into the query via parameters. Any assistance would be greatly appreciated. Thanks in advance!
推荐答案
除非我遗漏了什么,看来你可以加入 year/quarter 似乎没有必要对数据进行透视:
Unless I am missing something, it looks like you can just join the tables on the year/quarter there doesn't seem to be a need to pivot the data:
select t1.year, t1.quarter, t1.name t1Name, t1.state t1State, t1.mail t1Mail, t2.name t2Name, t2.state t2State, t2.mail t2Mail from table1 t1 inner join table2 t2 on t1.year = t2.year and t1.quarter = t2.quarter where t1.year = 2012 and t1.quarter = 1;
参见SQL Fiddle with Demo
现在如果有关于 year 和 quarter 是否存在于两个表中的问题,那么您可以使用 FULL OUTER JOIN代码>:
Now if there is a question on whether or not the year and quarter will exist in both tables, then you could use a FULL OUTER JOIN:
select coalesce(t1.year, t2.year) year, coalesce(t1.quarter, t2.quarter) quarter, t1.name t1Name, t1.state t1State, t1.mail t1Mail, t2.name t2Name, t2.state t2State, t2.mail t2Mail from table1 t1 full outer join table2 t2 on t1.year = t2.year and t1.quarter = t2.quarter where (t1.year = 2012 and t1.quarter = 2) or (t2.year = 2012 and t2.quarter = 2)
参见SQL Fiddle with Demo