table1字段
bh name
100 张三
200 李四
300 王五
table2
字段bh khdate age
100 2013-05-13 8:00:00 55
200 2013-05-13 8:00:00 60
100 2012-06-20 8:00:00 69
200 2012-06-20 8:00:00 80我想得到的结果是
1:按 日期khdate =2013-05-13 8:00:00 bh name khdate age
100 张三 2013-05-13 8:00:00 55
200 李四 2013-05-13 8:00:00 60
300 王五2: 按 日期khdate =2012-06-20 8:00:00 bh name khdate age
100 张三 2012-06-20 8:00:00 69
200 李四 2012-06-20 8:00:00 80
300 王五3: 按 日期khdate =2014-07-20 8:00:00
bh name khdate age
100 张三
200 李四
300 王五这样一个查询
bh name
100 张三
200 李四
300 王五
table2
字段bh khdate age
100 2013-05-13 8:00:00 55
200 2013-05-13 8:00:00 60
100 2012-06-20 8:00:00 69
200 2012-06-20 8:00:00 80我想得到的结果是
1:按 日期khdate =2013-05-13 8:00:00 bh name khdate age
100 张三 2013-05-13 8:00:00 55
200 李四 2013-05-13 8:00:00 60
300 王五2: 按 日期khdate =2012-06-20 8:00:00 bh name khdate age
100 张三 2012-06-20 8:00:00 69
200 李四 2012-06-20 8:00:00 80
300 王五3: 按 日期khdate =2014-07-20 8:00:00
bh name khdate age
100 张三
200 李四
300 王五这样一个查询
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-13 09:52:30
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([bh] int,[khdate] datetime,[age] int)
insert [table2]
select 100,'2013-05-13 8:00:00',55 union all
select 200,'2013-05-13 8:00:00',60 union all
select 100,'2012-06-20 8:00:00',69 union all
select 200,'2012-06-20 8:00:00',80--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([bh] int,[name] varchar(4))
insert [table1]
select 100,'张三' union all
select 200,'李四' union all
select 300,'王五'
--------------开始查询--------------------------
SELECT a.bh ,
a.NAME ,
b.khdate ,
b.age
FROM [table1] a
LEFT JOIN (SELECT * FROM [table2] WHERE khdate ='2013-05-13 8:00:00') b ON A.bh = b.bh
SELECT a.bh ,
a.NAME ,
b.khdate ,
b.age
FROM [table1] a
LEFT JOIN ( SELECT *
FROM [table2]
WHERE khdate = '2012-06-20 8:00:00'
) b ON A.bh = b.bhSELECT a.bh ,
a.NAME ,
b.khdate ,
b.age
FROM [table1] a
LEFT JOIN (SELECT * FROM [table2] WHERE khdate ='2014-07-20 8:00:00') b ON A.bh = b.bh
----------------结果----------------------------
/* bh NAME khdate age
----------- ---- ----------------------- -----------
100 张三 2013-05-13 08:00:00.000 55
200 李四 2013-05-13 08:00:00.000 60
300 王五 NULL NULL(3 行受影响)bh NAME khdate age
----------- ---- ----------------------- -----------
100 张三 2012-06-20 08:00:00.000 69
200 李四 2012-06-20 08:00:00.000 80
300 王五 NULL NULL(3 行受影响)bh NAME khdate age
----------- ---- ----------------------- -----------
100 张三 NULL NULL
200 李四 NULL NULL
300 王五 NULL NULL
*/