连接查询
样例数据；
Table-A 数据
date, code
2013-5-9,1
2013-5-9,2
2013-5-10,1
2013-5-10,2
2013-5-10,3
Table-B 数据
Code
1
2
3
4
怎样得到下面的结果
2013-5-9,1
2013-5-9,2
null, 3
null, 4
2013-5-10,1
2013-5-10,2
2013-5-10,3
null,4
[解决办法]
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
*/
--楼主试试，你是不是你要的
if exists(select * from sysobjects where name= 'a')
drop table a
go
create table a
(
date datetime,
code int
)
go
if exists(select * from sysobjects where name= 'b')
drop table b
go
create table b
(
code int
)
go
insert into 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
insert into b
select 1 union all
select 2 union all
select 3 union all
select 4
go
select * from a
select * from b
go
select b.code,a.date from
(
select b.code,a.date from b,a group by b.code,a.date
)b
left join a on a.code =b.code and a.date = b.date
