問(wèn)題描述

我有兩個(gè)表，每個(gè)表都將自己的結(jié)果集作為一行生成.我想將這些結(jié)果集合并為一行.例如:

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

結(jié)果:

Year  Quarter  Name  State  Mail
2012  1        Bob   NY     bob@gmail

查詢 #2:

SELECT *
FROM Table 2
WHERE Year = 2012 AND Quarter = 1

結(jié)果:

Year  Quarter  Name  State  Mail
2012  1        Greg  DC     greg@gmail

期望的結(jié)果集:

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

結(jié)果被加入/旋轉(zhuǎn)到 Year 和 Quarter 的組合上，這將通過(guò)參數(shù)輸入到查詢中.任何幫助將不勝感激.提前致謝！

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!

推薦答案

除非我遺漏了什么，看來(lái)你可以加入 year/quarter 似乎沒(méi)有必要對(duì)數(shù)據(jù)進(jìn)行透視:

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;

參見(jiàn)SQL Fiddle with Demo

現(xiàn)在如果有關(guān)于 year 和 quarter 是否存在于兩個(gè)表中的問(wèn)題，那么您可以使用 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)