日期:2014-05-17 浏览次数:20479 次
select a.code,b.code from a
right join b
on datepart(day,date)=b.code
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-09 16:17:40
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([date] datetime,[code] int)
insert [A]
select '2013-5-9',1 union all
select '2013-5-9',2 union all
select '2013-5-10',1 union all
select '2013-5-10',2 union all
select '2013-5-10',3
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([Code] int)
insert [B]
select 1 union all
select 2 union all
select 3 union all
select 4
--------------开始查询--------------------------
SELECT b.code,a.[date]
FROM (
SELECT *
FROM b cross JOIN (SELECT DISTINCT [date] FROM a) a )b LEFT JOIN a ON a.[code]=b.[code] AND a.[date]=b.[date]
----------------结果----------------------------
/*
code date
----------- -----------------------
1 2013-05-09 00:00:00.000
2 2013-05-09 00:00:00.000
3 NULL
4 NULL
1 2013-05-10 00:00:00.000
2 2013-05-10 00:00:00.000
3 2013-05-10 00:00:00.000
4 NULL
*/