sql两表查询.得到其中一个表的全部数据.该怎么查询
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 王五
这样一个查询
[解决办法]
----------------------------
-- 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.bh
SELECT 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
*/
