我有两个表。第一个是
name role
XXX engineer
yyy tester
zzz developer
第二个表是
name role
xxx tester
yyy tester
aaa developer
我需要结果集
name role name role
xxx engineer xxx tester
zzz developer aaa developer
请提供解决方案..
查询之后可以为您提供帮助。在这里,我以为您是通过行索引匹配数据。手段,同一行索引上的数据必须相同。
--CREATE TABLE 1
DECLARE @TBL1 AS TABLE(name varchar(10), role varchar(10))
INSERT INTO @TBL1
VALUES
('XXX', 'engineer'),
('yyy', 'tester'),
('zzz', 'developer')
--CREATE TABLE 2
DECLARE @TBL2 AS TABLE(name varchar(10), role varchar(10))
INSERT INTO @TBL2
VALUES
('xxx', 'tester'),
('yyy', 'tester'),
('aaa', 'developer')
--Final Query
Select T1.name, T1.role, T2.name, T2.role
From (Select *, ROW_NUMBER() OVER(ORDER BY GETDATE()) as RNr from @TBL1) T1
Inner Join (Select *, ROW_NUMBER() OVER(ORDER BY GETDATE()) as RNr from @TBL2) T2 on T1.RNr = T2.RNr
Where (T1.name != T2.name OR T1.role != T2.role)
在最终查询中,我编写了两个子查询,以生成两个表的行号。然后,我按行号和检查条件不匹配的地方加入了两个表。