求助：两张表的联合查询
表A:
select 语句如下:
select id,datatime,z,wt,q from A where datediff(hh,datatime,getdate())=0 and id in(select id from C)
查询结果如下：
ID datatime z wt q
1 2012-05-04 21:00:000 123 23.5 234
2 2012-05-04 21:00:000 134 45.6 456
3 2012-05-04 21:00:000 345 89.0 987
4 2012-05-04 21:00:000 445 89.0 787
5 2012-05-04 21:00:000 365 39.0 287
6 2012-05-04 21:00:000 765 89.0 787
7 2012-05-04 21:00:000 145 59.0 677
8 ......
9......
....
15 2012-05-04 21:00:000 645 89.0 987
表A查询的结果是上面的15条数据。
----------------------------------
表B:
select 语句如下：
select id,datatime,tt from B where datediff(hh,datatime,getdate())=0 and id in(select id from C)
查询结果如下;
ID datatime tt
1 2012-05-04 21:00:000 0.5
2 2012-05-04 21:00:000 1
4 2012-05-04 21:00:000 1.5
5 2012-05-04 21:00:000 3
7 2012-05-04 21:00:000 2.5
8 ......
9......
....
15 2012-05-04 21:00:000 1
------------------------------------------------------
表A和表B中的ID都是从表C中来的，表B的查询结果比表A少了两条，就是ID=3和ID=6的没有，我现在要得到如下的结果：
ID atatiem z wt q tt
1 2012-05-04 21:00:000 123 23.5 234 0.5
2 2012-05-04 21:00:000 134 45.6 456 1
3 2012-05-04 21:00:000 345 89.0 987 null
4 2012-05-04 21:00:000 445 89.0 787 1.5
5 2012-05-04 21:00:000 365 39.0 287 3
6 2012-05-04 21:00:000 765 89.0 787 null
7 2012-05-04 21:00:000 145 59.0 677 2.5
8 ......
9 ......
10
11
12
13
14 ....
15 2012-05-04 21:00:000 645 89.0 987 2.5
我写的select 语句如下：
select a.id ,a.datatime,a.z ,a.wt,a.q,b,tt from A a,B b
where a.id=b.id and datediff(hh,a.datatime,getdate())=0
and a.id in (select STCD from C) and a.tm=b.tm
order by a.stcd
得到的结果也是没有ID=3和ID=6的数据，请问这个语句该怎么写？
[解决办法]
select a.id ,a.datatime,a.z ,a.wt,a.q,b,tt from A a Right Join B b On (a.tm=b.tm )
where a.id=b.id and datediff(hh,a.datatime,getdate())=0
and b.id in (select STCD from C)
order by a.stcd
[解决办法]
select a.id ,a.datatime,a.z ,a.wt,a.q,b,tt from B b left Join A a On (a.tm=b.tm )where a.id=b.id and datediff(hh,a.datatime,getdate())=0 and b.id in (select STCD from C) order by a.stcd
[解决办法]
select datediff(hh,'2012-05-04 21:00:000',getdate())
执行结果为-12
select datediff(dd,'2012-05-04 21:00:000',getdate())
执行结果为0
[解决办法]
select id,datatime,z,wt,q,tt from A INNER JOIN C ON datediff(hh,datatime,getdate())=0 and A.id = C.id LEFT OUTER JOIN B ON datediff(hh,datatime,getdate())=0
[解决办法]
select A.ID,A.DataTime,A.z,A.wt,A.q,B.tt from A left join C on A.Id=B.ID and a.tm=b.tmwhere datediff(hh,A.datatime,getdate())=0 and datediff(hh,B.datatime,getdate())=0 and A.ID in(select ID from C)order by A.stcd
[解决办法]
估計B表數據問題你看下 ID=3 和 ID=6 的Datetime字段有沒有出錯
[解决办法]
要取A表中所有数据，应以A表为基础表做连接。
select a.id,a.datatime,a.z,a.wt,a.q,b.tt from A aleft join B b on a.id=b.id and datediff(hh,a.datatime,getdate())=0 and datediff(hh,b.datatime,getdate())=0left join C c on a.id=c.id
[解决办法]
if object_id('A') is not null drop table A
create table A (id int,datetime datetime,z decimal(10,3))
insert A
select 1, '2012-05-06 17:00:00.000', 171.950 union
select 2, '2012-05-06 17:00:00.000', 138.460 union
select 3, '2012-05-06 17:00:00.000', 89.390 union
select 4, '2012-05-06 17:00:00.000', 88.980 union
select 5, '2012-05-06 17:00:00.000', 64.600 union
select 6, '2012-05-06 17:00:00.000', 56.540 union
select 7, '2012-05-06 17:00:00.000', 50.960 union
select 8, '2012-05-06 17:00:00.000', 238.630 union
select 9, '2012-05-06 17:00:00.000', 192.260 union
select 10, '2012-05-06 17:00:00.000', 159.520 union
select 11, '2012-05-06 17:00:00.000', 84.710 union
select 12, '2012-05-06 17:00:00.000', 81.990 union
select 13, '2012-05-06 17:00:00.000', 76.260 union
select 14, '2012-05-06 17:00:00.000', 78.650 union
select 15, '2012-05-06 17:00:00.000', 78.690 union
select * from A
GO
if object_id('B') is not null drop table B
create table B (id int,datetime datetime,tt decimal(10,1))
insert B
select 1, '2012-05-06 17:00:00.000', 0.0 union
select 2, '2012-05-06 17:00:00.000', 0.0 union
select 4, '2012-05-06 17:00:00.000', 0.00 union
select 5, '2012-05-06 17:00:00.000', 0.00 union
select 7, '2012-05-06 17:00:00.000', 0.0 union
select 8, '2012-05-06 17:00:00.000', 0.0 union
select 9, '2012-05-06 17:00:00.000', 0.0 union
select 10, '2012-05-06 17:00:00.000', 0.0 union
select 11, '2012-05-06 17:00:00.000', 0.0 union
select 12, '2012-05-06 17:00:00.000', 0.0 union
select 13, '2012-05-06 17:00:00.000', 0.0 union
select 14, '2012-05-06 17:00:00.000', 0.0 union
select 15, '2012-05-06 17:00:00.000', 0.0 union
select * from B
GO
select A.id,A.datetime,A.z,B.tt from A left join B on A.id=B.id
/*结果：
12012-05-06 17:00:00.000171.950.0
22012-05-06 17:00:00.000138.460.0
32012-05-06 17:00:00.00089.390NULL
42012-05-06 17:00:00.00088.980.0
52012-05-06 17:00:00.00064.600.0
62012-05-06 17:00:00.00056.540NULL
72012-05-06 17:00:00.00050.960.0
82012-05-06 17:00:00.000238.630.0
92012-05-06 17:00:00.000192.260.0
102012-05-06 17:00:00.000159.520.0
112012-05-06 17:00:00.00084.710.0
122012-05-06 17:00:00.00081.990.0
132012-05-06 17:00:00.00076.260.0
142012-05-06 17:00:00.00078.650.0
152012-05-06 17:00:00.00078.690.0
*/
