test1
a b c
1 2 3
2 null 4
3 4 5test2
a b d
1 2 4
2 3 6
3 4 5结果:
a b c d
1 2 3 4
2 null 4 6
3 4 5 5应该有SQL可以实现吧
a b c
1 2 3
2 null 4
3 4 5test2
a b d
1 2 4
2 3 6
3 4 5结果:
a b c d
1 2 3 4
2 null 4 6
3 4 5 5应该有SQL可以实现吧
加个row_number 然后做联接
b,
c,
d
from test a full jion test2 b
on a.a=b.a
from test1 a,test2
where a.a=b.a
select isnull(a.a,b.a)a,
b,
c,
d
from test a full join test2 b
on a.a=b.a
Select Test1.a,Test1.b,Test1.c,Test2.d from Test1
Join test2 on Test1.a = Test2.a
--> 生成测试数据表: [test1]
IF OBJECT_ID('[test1]') IS NOT NULL
DROP TABLE [test1]
GO
CREATE TABLE [test1] ([a] [int],[b] [int],[c] [int])
INSERT INTO [test1]
SELECT '1','2','3' UNION ALL
SELECT '2',NULL,'4' UNION ALL
SELECT '3','4','5'--> 生成测试数据表: [test2]
IF OBJECT_ID('[test2]') IS NOT NULL
DROP TABLE [test2]
GO
CREATE TABLE [test2] ([a] [int],[b] [int],[d] [int])
INSERT INTO [test2]
SELECT '1','2','4' UNION ALL
SELECT '2','3','6' UNION ALL
SELECT '3','4','5'--SELECT * FROM [test1]
--SELECT * FROM [test2]-->SQL查询如下:
SELECT a.*, b.d
FROM test1 a
FULL JOIN test2 b
ON a.a = b.a
/*
a b c d
----------- ----------- ----------- -----------
1 2 3 4
2 NULL 4 6
3 4 5 5(3 行受影响)
*/
SELECT a.*, b.d
FROM test1 a
LEFT JOIN test2 b
ON a.a = b.a
/*
a b c d
----------- ----------- ----------- -----------
1 2 3 4
2 NULL 4 6
3 4 5 5(3 行受影响)
*/
SELECT a.*, b.d
FROM test1 a
JOIN test2 b
ON a.a = b.a
/*
a b c d
----------- ----------- ----------- -----------
1 2 3 4
2 NULL 4 6
3 4 5 5(3 行受影响)
*/
left join
test2 as b on a.a = b.a
drop table test
Go
Create table test([a] nvarchar(2),[b] int,[c] int)
Insert test
select N'小王',1,4 union all
select N'小李',2,3 union all
select N'小刀',null,null
Go
if not object_id('test2') is null
drop table test2
Go
Create table test2([a] nvarchar(2),[d] int,[e] int)
Insert test2
select N'小王',3,4 union all
select N'小李',6,7
GoSELECT A,MAX(B)B,MAX(C)C,MAX(D)D,MAX(E)
FROM
(
SELECT A,B,C,NULL AS D,NULL AS E FROM test
UNION ALL
SELECT A,NULL,NULL,D,E FROM test2
) T
GROUP BY A/*
(所影响的行数为 3 行)
(所影响的行数为 2 行)A B C D
---- ----------- ----------- ----------- -----------
小刀 NULL NULL NULL NULL
小李 2 3 6 7
小王 1 4 3 4(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。
a b c
1 2 3
2 null 4
3 4 5test2
a b d
1 2 4
1 1 4
2 3 6
3 4 5结果:
a b c d
1 2 3 4
2 null 4 6
3 4 5 5我要判断两个的,上面的表没写好,看看这个