LEFT OUTER JOIN

[Dision LI]

 SELECT PAPersonInfo.*,w.sex
 FROM   PAPersonInfo  --这个主表有360条数据

        LEFT OUTER JOIN datasource..AttenResultA A ON A.EmpID = PAPersonInfo.EmpID
 WHERE  A.Yea = YEAR('2010-08-30')
        AND A.mon = MONTH('2010-08-30')  
--关联上从表怎么数据变少了,有些数据在从表不存在

我想显示主表所有的360条数据,从表的数据有则显示，无则为空

[「已注销」]

你这个左连接没有问题的呀

[fpzgm]

w.sex?

[dawugui]

SELECT PAPersonInfo.*,w.sex
FROM   PAPersonInfo  --这个主表有360条数据
LEFT OUTER JOIN datasource..AttenResultA A ON A.EmpID = PAPersonInfo.EmpID
and A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH('2010-08-30')  

SELECT PAPersonInfo.*,w.sex
FROM   PAPersonInfo  --这个主表有360条数据
LEFT OUTER JOIN datasource..AttenResultA A ON A.EmpID = PAPersonInfo.EmpID
and convert(varchar(7),A.Yea,120) = '2010-08'

[「已注销」]

WHERE A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH('2010-08-30')
====
把这些都写到on 里面去
SELECT PAPersonInfo.*,w.sex
FROM PAPersonInfo --这个主表有360条数据

LEFT OUTER JOIN datasource..AttenResultA A
ON A.EmpID = PAPersonInfo.EmpID
and A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH('2010-08-30')

[jwdream2008]

[Quote=引用 3 楼 dawugui 的回复:]
SQL code
SELECT PAPersonInfo.*,w.sex
FROM PAPersonInfo --这个主表有360条数据
LEFT OUTER JOIN datasource..AttenResultA A ON A.EmpID = PAPersonInfo.EmpID
and A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH……
[/Quote]

[dawugui]

SELECT PAPersonInfo.*,a.sex
FROM   PAPersonInfo  --这个主表有360条数据
LEFT OUTER JOIN datasource..AttenResultA A ON PAPersonInfo.EmpID = A.EmpID 
and A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH('2010-08-30')  

SELECT PAPersonInfo.*,w.sex
FROM   PAPersonInfo  --这个主表有360条数据
LEFT OUTER JOIN datasource..AttenResultA A ON PAPersonInfo.EmpID = A.EmpID 
and convert(varchar(7),A.Yea,120) = '2010-08'

[--小F--]

SELECT PAPersonInfo.*,w.sex
FROM PAPersonInfo --这个主表有360条数据

LEFT OUTER JOIN datasource..AttenResultA A ON A.EmpID = PAPersonInfo.EmpID
and A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH('2010-08-30')

[dawugui]

上面的应该只要（我还以为你的yea是时间字段，其实你只是个年份字段）：

SELECT PAPersonInfo.*,a.sex
FROM   PAPersonInfo  --这个主表有360条数据
LEFT OUTER JOIN datasource..AttenResultA A ON PAPersonInfo.EmpID = A.EmpID 
and A.Yea = YEAR('2010-08-30')
AND A.mon = MONTH('2010-08-30')  

[fpzgm]

将where 条件写到on里面

因为主表的有的数据不满足where后的条件就没了