34,590
社区成员
发帖
与我相关
我的任务
分享
Go
SELECT a.日期1,ISNULL(a.品名,b.品名) AS 品名,b.日期2 FROM (SELECT ROW_NUMBER()OVER(PARTITION BY [品名] ORDER BY [日期1]) AS RN,* FROM #a) AS a FULL JOIN (SELECT ROW_NUMBER()OVER(PARTITION BY [品名] ORDER BY [日期2]) AS RN,* FROM #b) as b ON a.品名=b.品名 AND a.RN=b.RN
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([id] int,[品名] nvarchar(22),[日期1] Date)
Insert #a
select 1,N'a1','2011.1.1' union all
select 2,N'a1','2011.2.1' union all
select 3,N'a1','2011.3.1' union all
select 4,N'a2','2011.4.1'
Go
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#b') is null
drop table #b
Go
Create table #b([id] int,[品名] nvarchar(22),[日期2] Date)
Insert #b
select 1,N'a1','2012.1.1' union all
select 2,N'a1','2012.2.1' union all
select 3,N'a3','2012.4.1'
Go
SELECT a.日期1,ISNULL(a.品名,b.品名) AS 品名,b.日期2 FROM #a AS a FULL JOIN #b as b ON a.品名=b.品名 AND DATEADD(yy,1,a.日期1)=b.日期2
/*
日期1 品名 日期2
2011-01-01 a1 2012-01-01
2011-02-01 a1 2012-02-01
2011-03-01 a1 NULL
2011-04-01 a2 NULL
NULL a3 2012-04-01
*/
SELECT a.日期1,ISNULL(a.品名,b.品名) AS 品名,b.日期2 FROM a FULL JOIN b ON a.品名=b.品名 AND a.日期1=b.日期2
用full join