So here's my setup I have 2 tables Old and New with the following (simplified) schemas
这是我的设置,我有2个表,旧的和新的,有下面的(简化的)模式。
Old
[ManName]
[ManNumber]
New
[Manager_Name]
[Manager_Number]
I'm looking to craft a SQL query that returns the following but in 4 columns in 1 query as opposed to 2 queries with 2 columns each
我希望编写一个SQL查询,它返回以下内容,但在1个查询中的4个列中,与2个列的2个查询相反。
select distinct manname, mannumber from OLD
select distinct Manager_Name, Manager_Number from NEW
So my ideal Result Set would have 4 columns:
所以理想的结果集是4列
ManName ManNumber Manager_Name Manager Number
Thanks!
谢谢!
2 个解决方案
#1
1
have you tried a JOIN statement:
你是否尝试过加入声明:
select distinct *
from old a join new b on a.ManName = b. Manager_Name and a. ManNumber = b. Manager_Number
#2
1
Using pretty much any RDBMS except MySQL, you can do it with CTEs and ROW_NUMBER()
;
除了MySQL之外,使用几乎任何RDBMS,都可以使用CTEs和ROW_NUMBER();
WITH cteOld AS (
SELECT DISTINCT ManName, ManNumber,
ROW_NUMBER() OVER (ORDER BY ManNumber) rn FROM Old
), cteNew AS (
SELECT DISTINCT Manager_Name, Manager_Number,
ROW_NUMBER() OVER (ORDER BY Manager_Number) rn FROM New
)
SELECT ManName, ManNumber, Manager_Name, Manager_Number
FROM cteOld
FULL OUTER JOIN cteNew
ON cteOld.rn=cteNew.rn
一个用来测试的SQLfiddle。
Another, easier, way to just get all of them is to simply use a UNION;
另一个更简单的方法就是简单地使用一个联合;
SELECT ManName, ManNumber, 0 isNew FROM Old
UNION
SELECT Manager_Name, Manager_Number, 1 FROM New
另一个SQLfiddle。
#1
1
have you tried a JOIN statement:
你是否尝试过加入声明:
select distinct *
from old a join new b on a.ManName = b. Manager_Name and a. ManNumber = b. Manager_Number
#2
1
Using pretty much any RDBMS except MySQL, you can do it with CTEs and ROW_NUMBER()
;
除了MySQL之外,使用几乎任何RDBMS,都可以使用CTEs和ROW_NUMBER();
WITH cteOld AS (
SELECT DISTINCT ManName, ManNumber,
ROW_NUMBER() OVER (ORDER BY ManNumber) rn FROM Old
), cteNew AS (
SELECT DISTINCT Manager_Name, Manager_Number,
ROW_NUMBER() OVER (ORDER BY Manager_Number) rn FROM New
)
SELECT ManName, ManNumber, Manager_Name, Manager_Number
FROM cteOld
FULL OUTER JOIN cteNew
ON cteOld.rn=cteNew.rn
一个用来测试的SQLfiddle。
Another, easier, way to just get all of them is to simply use a UNION;
另一个更简单的方法就是简单地使用一个联合;
SELECT ManName, ManNumber, 0 isNew FROM Old
UNION
SELECT Manager_Name, Manager_Number, 1 FROM New
另一个SQLfiddle。