T-SQL: Joining result-sets horizontally(T-SQL:水平连接结果集)
问题描述
我有两个表,每个表都将自己的结果集作为一行生成.我想将这些结果集合并为一行.例如:
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
这篇关于T-SQL:水平连接结果集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:T-SQL:水平连接结果集
基础教程推荐
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01