一个关联查询的问题，感觉好怪

[zsjzwj]

2个表分别查的数据

select * from pod where po_id='yd-001791'
查询结果
YD-001791 0 PR13-00004-79 1137 1155
YD-001791 0 PR13-00004-79 3491 1155
YD-001791 0 PR13-00004-79 3492 1155
YD-001791 0 PR13-00004-79 3493 1155
YD-001791 0 PR13-00004-79 3495 1155

select * from POQSSum1_view where po_id='yd-001791'
查询结果
YD-001791 PR13-00004-79 1137 1155 20.00000000000 20.00000000000
YD-001791 PR13-00004-79 3491 1155 EA EA .00000000000 .00000000000
YD-001791 PR13-00004-79 3492 1155 EA EA .00000000000 .00000000000
YD-001791 PR13-00004-79 3493 1155 EA EA .00000000000 .00000000000
YD-001791 PR13-00004-79 3495 1155 EA EA .00000000000 .00000000000


关联查询语句一：
select * from pod A
left join dbo.POQSSum1_view I
on A.wl_id=I.wl_id and A.ssn_id=I.ssn_id and A.po_id=I.po_id and A.col_id=I.col_id
where A.po_id='YD-001791'

关联查询语句二：
select * from pod A
left join dbo.POQSSum1_view I
on A.wl_id=I.wl_id and A.ssn_id=I.ssn_id and A.po_id=I.po_id and A.col_id=I.col_id
where I.po_id='YD-001791'


关联查询的两个语句就是 where 中用A还是I的字段做条件，结果却不同，

什么原因呀？？？？？？？？？？？？？？

[dawugui]

关联查询语句一：
select * from pod A
left join dbo.POQSSum1_view I   
  on A.wl_id=I.wl_id and A.ssn_id=I.ssn_id and A.po_id=I.po_id and A.col_id=I.col_id   
where A.po_id='YD-001791'

这个从pod A这个表去筛选。

关联查询语句二：
select * from pod A
left join dbo.POQSSum1_view I   
  on A.wl_id=I.wl_id and A.ssn_id=I.ssn_id and A.po_id=I.po_id and A.col_id=I.col_id   
where I.po_id='YD-001791'

这个从dbo.POQSSum1_view I这个表去筛选。

两个条件不同，结果可能不同，很正常的嘛。

不知道如下的例,你是否能看明白?

create table t1(id int , val int)
insert into t1 values(1 , 1)
insert into t1 values(2 , 1)
insert into t1 values(3 , 2)
insert into t1 values(4 , 2)
insert into t1 values(5 , 3)
create table t2(id int , val int)
insert into t2 values(1 , 1)
insert into t2 values(2 , 2)
insert into t2 values(3 , 1)
insert into t2 values(4 , 2)
insert into t2 values(5 , 3)
go

select t1.* , t2.* from t1 left join t2 on t1.id = t2.id where t1.val = 1
/*
id          val         id          val         
----------- ----------- ----------- ----------- 
1           1           1           1
2           1           2           2

（所影响的行数为 2 行）
*/

select t1.* , t2.* from t1 left join t2 on t1.id = t2.id where t2.val = 1

drop table t1 , t2
/*
id          val         id          val         
----------- ----------- ----------- ----------- 
1           1           1           1
3           2           3           1

（所影响的行数为 2 行）
*/

[nalnait]

[Quote=引用 1 楼 dawugui 的回复:]
SQL code
关联查询语句一：
select * from pod A
left join dbo.POQSSum1_view I
on A.wl_id=I.wl_id and A.ssn_id=I.ssn_id and A.po_id=I.po_id and A.col_id=I.col_id
where A.po_id='YD-001791'

这个从pod ……
[/Quote]学习

[htl258_Tony]

LEFT JOIN 为左联接,也就是说左表跟右表不管怎么样,左表还是都显示,右表在不满足关联条件的情况下,不满足的行不显示,这样一来,左右表的行数不同,自然用左表的筛选的条件和右表比起来结果存在不同是正常的.看以下简例:

create table #lefttb
(
[name]    varchar(20),
[date]    datetime
)

insert into #lefttb
select 'A','2009-01-05' union all
select 'B','2009-01-06' 
GO
create table #righttb
(
[name]    varchar(20),
[date]    datetime
)

insert into #righttb
select 'C','2009-01-05' union all
select 'D','2009-01-06' 


select * from #lefttb a left join #righttb b on a.name=b.name
/*
name                 date                    name                 date
-------------------- ----------------------- -------------------- -----------------------
A                    2009-01-05 00:00:00.000 NULL                 NULL
B                    2009-01-06 00:00:00.000 NULL                 NULL

(2 行受影响)
*/
select * from #lefttb a left join #righttb b on a.name=b.name where a.date='2009-01-05'
/*
name                 date                    name                 date
-------------------- ----------------------- -------------------- -----------------------
A                    2009-01-05 00:00:00.000 NULL                 NULL

(1 行受影响)
*/
select * from #lefttb a left join #righttb b on a.name=b.name where b.date='2009-01-05'
/*
name                 date                    name                 date
-------------------- ----------------------- -------------------- -----------------------

(0 行受影响)
*/

create table #lefttb
(
[name] varchar(20),
[date] datetime
)

insert into #lefttb
select 'A','2009-01-05' union all
select 'B','2009-01-06'
GO
create table #righttb
(
[name] varchar(20),
[date] datetime
)

insert into #righttb
select 'C','2009-01-05' union all
select 'D','2009-01-06'


select * from #lefttb a left join #righttb b on a.name=b.name
/*
name date name date
-------------------- ----------------------- -------------------- -----------------------
A 2009-01-05 00:00:00.000 NULL NULL
B 2009-01-06 00:00:00.000 NULL NULL

(2 行受影响)
*/
select * from #lefttb a left join #righttb b on a.name=b.name where a.date='2009-01-05'
/*
name date name date
-------------------- ----------------------- -------------------- -----------------------
A 2009-01-05 00:00:00.000 NULL NULL

(1 行受影响)
*/
select * from #lefttb a left join #righttb b on a.name=b.name where b.date='2009-01-05'
/*
name date name date
-------------------- ----------------------- -------------------- -----------------------

(0 行受影响)
*/

[喜-喜]

楼主很慷慨，很阔气，发了两贴来散分来了！....................

[--小F--]

后面是不同表的字段 所以筛选条件不一样 结果当然不一样

[ws_hgo]

你的这个条件
where A.po_id='YD-001791'
和
where I.po_id='YD-001791'
条件不一样
结果肯定不一样的

[ws_hgo]

你的这个条件
where A.po_id='YD-001791'
和
where I.po_id='YD-001791'
条件不一样
结果肯定不一样的

[Jessie_Zhang]

呵呵...发了2次?

[叶子]

条件不同，结果不同很正常。

[rfq]

学习中

[无锡阔微信息科技]

结果肯定不一样吗，你看看left join的用法就全明白了