两张表拼成一张表的问题！大神请进！！！

[zhx2595922]

a表：
id time
‘a’ 2016
'b' 2016
b表：
test id time
666 ‘a’ 2017
666 'b' 2017

结果：
test id time
666 'a' 2017
null 'a' 2016
666 'b' 2017
null 'b' 2016

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
CREATE TABLE a(id VARCHAR(20),[time] INT)
CREATE TABLE b(test INT,id VARCHAR(20),[time] INT)
SET NOCOUNT ON
INSERT INTO a(id,[time]) VALUES('a',2016)
INSERT INTO a(id,[time]) VALUES('b',2016)
INSERT INTO b(test,id,[time]) VALUES(666,'a',2017)
INSERT INTO b(test,id,[time]) VALUES(666,'b',2017)

SELECT * FROM (
SELECT NULL AS test,id,[time] FROM a
UNION ALL
SELECT test,id,[time] FROM b
) AS t
ORDER BY t.id ASC
/*
test	id	time
NULL	a	2016
666	    a	2017
666	    b	2017
NULL	b	2016
*/

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(21),[time] Date)
Insert #A
select N'a','2016' union all
select N'b','2016'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([test] int,[id] nvarchar(21),[time] Date)
Insert #B
select 666,N'a','2017' union all
select 666,N'b','2017'
Go
--测试数据结束
;WITH ctea AS (
SELECT NULL AS test,id,time,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #A
UNION
SELECT *,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #B
)
SELECT test,id,time FROM ctea ORDER BY num,time DESC

[zhx2595922]

引用 1 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
IF OBJECT_ID('b') IS NOT NULL DROP TABLE b
CREATE TABLE a(id VARCHAR(20),[time] INT)
CREATE TABLE b(test INT,id VARCHAR(20),[time] INT)
SET NOCOUNT ON
INSERT INTO a(id,[time]) VALUES('a',2016)
INSERT INTO a(id,[time]) VALUES('b',2016)
INSERT INTO b(test,id,[time]) VALUES(666,'a',2017)
INSERT INTO b(test,id,[time]) VALUES(666,'b',2017)

SELECT * FROM (
SELECT NULL AS test,id,[time] FROM a
UNION ALL
SELECT test,id,[time] FROM b
) AS t
ORDER BY t.id ASC
/*
test	id	time
NULL	a	2016
666	    a	2017
666	    b	2017
NULL	b	2016
*/

感谢女神，我想要的null并不是自己写的，而是，比如left join那样的null

[zhx2595922]

引用 2 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(21),[time] Date)
Insert #A
select N'a','2016' union all
select N'b','2016'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([test] int,[id] nvarchar(21),[time] Date)
Insert #B
select 666,N'a','2017' union all
select 666,N'b','2017'
Go
--测试数据结束
;WITH ctea AS (
SELECT NULL AS test,id,time,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #A
UNION
SELECT *,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #B
)
SELECT test,id,time FROM ctea ORDER BY num,time DESC

其实我想的是，，是否可以用left join实现。。。

[二月十六]

引用 4 楼 zhx2595922 的回复:

[quote=引用 2 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(21),[time] Date)
Insert #A
select N'a','2016' union all
select N'b','2016'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([test] int,[id] nvarchar(21),[time] Date)
Insert #B
select 666,N'a','2017' union all
select 666,N'b','2017'
Go
--测试数据结束
;WITH ctea AS (
SELECT NULL AS test,id,time,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #A
UNION
SELECT *,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #B
)
SELECT test,id,time FROM ctea ORDER BY num,time DESC

其实我想的是，，是否可以用left join实现。。。[/quote] 为什么要执着链接？可以实现也比这个麻烦

[zhx2595922]

引用 5 楼 sinat_28984567 的回复:

[quote=引用 4 楼 zhx2595922 的回复:] [quote=引用 2 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(21),[time] Date)
Insert #A
select N'a','2016' union all
select N'b','2016'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([test] int,[id] nvarchar(21),[time] Date)
Insert #B
select 666,N'a','2017' union all
select 666,N'b','2017'
Go
--测试数据结束
;WITH ctea AS (
SELECT NULL AS test,id,time,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #A
UNION
SELECT *,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #B
)
SELECT test,id,time FROM ctea ORDER BY num,time DESC

其实我想的是，，是否可以用left join实现。。。[/quote] 为什么要执着链接？可以实现也比这个麻烦[/quote] 因为可能要处理上百个字段。。。不可能每个字段都null下吧？？

[二月十六]

引用 6 楼 zhx2595922 的回复:

[quote=引用 5 楼 sinat_28984567 的回复:] [quote=引用 4 楼 zhx2595922 的回复:] [quote=引用 2 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([id] nvarchar(21),[time] Date)
Insert #A
select N'a','2016' union all
select N'b','2016'
GO
if not object_id(N'Tempdb..#B') is null
	drop table #B
Go
Create table #B([test] int,[id] nvarchar(21),[time] Date)
Insert #B
select 666,N'a','2017' union all
select 666,N'b','2017'
Go
--测试数据结束
;WITH ctea AS (
SELECT NULL AS test,id,time,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #A
UNION
SELECT *,ROW_NUMBER()OVER(ORDER BY id) AS num FROM #B
)
SELECT test,id,time FROM ctea ORDER BY num,time DESC

其实我想的是，，是否可以用left join实现。。。[/quote] 为什么要执着链接？可以实现也比这个麻烦[/quote] 因为可能要处理上百个字段。。。不可能每个字段都null下吧？？[/quote] 还是用这个吧，因为join是关联，两个表关联会得到更多的列，而不是更多行，所以上边那个数据，left join后会剩两条数据，或者用别的方式实现了4条数据，但是比这个写null还要麻烦。 另外设计的表有上百个字段，可以重新考虑一下数据架构的问题