SQL Server:左联接所产生的行少于左表中的行 [英] SQL Server : left join results in fewer rows than in left table

问题描述

我正在使用SQL Server(我相信是2005年).

I am using SQL Server (I believe it is 2005).

我有TableA，它具有2列和439行(每行都是唯一的).

I have TableA that has 2 columns and 439 rows (each row is unique).

+----------+
|ID | Name |
+----------+

我有TableB，它具有35列和数十万行(每行也是唯一的).

I have TableB that has 35 columns and many hundreds of thousand rows (each row is also unique).

+------------------------------------------------------------------------------+
|Date | ID | Name | Blah1 | Blah2 | ... | Hour1 | Hour2 | Hour3 | ... | Hour24 |
+------------------------------------------------------------------------------+

TableB中的每一行都有每小时的观察和其他一些房屋管理信息.现在出于测试目的，我只对今天的日期(即2013年4月19日)感兴趣.

Each row in TableB has hourly observations and some other house keeping information. Now for testing purposes I am only interested in today's date i.e 4/19/2013.

如果我这样做:

Select count(*) 
from TableB 
where Date = '4/19/2013 12:00:00 AM'

我得到10526，这是正确的，因为每天有10526个不同的位置每小时都有观测数据.

I get 10526, which is correct as there are 10526 distinct locations for which there is hourly observation data each day.

我想左联接TableA和TableB on a.id = b.id，它们应该产生具有439行的结果.

I want to LEFT JOIN TableA and TableB on a.id = b.id, which SHOULD produce a result that has 439 rows.

不幸的是，结果有246行.怎么会这样? LEFT JOIN是否不应该返回TableA中的所有行，而不管TableB中是否存在匹配项?

Unfortunately, the result has 246 rows. How can this be? Isn't a LEFT JOIN suppose to return all rows in TableA regardless of whether there was a match in TableB?

* EDIT *

*EDIT*

我使用的完整查询是:

select * 
from TableA as a
left join TableB as b on a.id = b.id 
where RealDate = '4/20/2013 12:00:00 AM'

推荐答案

尝试一下:

select * from TableA as a
left join (SELECT * from TableB where RealDate = '4/20/2013 12:00:00 AM') as b
on a.id = b.id 

或者这个:

select * from TableA as a
left join TableB as b on (a.id = b.id AND RealDate = '4/20/2013 12:00:00 AM')

这篇关于SQL Server:左联接所产生的行少于左表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！