比如A: roomguid .... 1 壹号s_order roomguid .... 1 a 1 b这样结果就是 1 壹号 1 a 1 壹号 1 b多出来数据有什么奇怪么?
举个例子: ;with a as ( select 1 roomguid,'壹号' pname ),s_order as ( select 1 roomguid,'aa' vv union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' union all select 1 ,'aa' )select * from a left join s_order o on o.roomguid = a.roomguid where a.pname = '壹号' /*11条数据 roomguid pname roomguid vv 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa 1 壹号 1 aa */
因为text表RoomGUID字段与s_order表roomguid字段存在一对多的关系.
因为在s_order 表中有相同 roomguid 的多个行. create table test(roomguid nvarchar(20),pname nvarchar(20)) insert into test select 'aaaaa','壹号' insert into test select 'bbbbb','壹号' create table s_order(roomguid nvarchar(20)) insert into s_order select 'aaaaa' insert into s_order select 'aaaaa' go SELECT * FROM test A left join s_order o on a.RoomGUID=o.roomguid WHERE pname='壹号' /* roomguid pname roomguid -------------------- -------------------- -------------------- aaaaa 壹号 aaaaa aaaaa 壹号 aaaaa bbbbb 壹号 NULL(3 行受影响) */ go drop table test,s_order
所以这样的话,直接:SELECT * FROM A WHERE pname='壹号' 就1条,而关联之后: select * from a left join s_order o on o.roomguid = a.roomguid where a.pname = '壹号'就是11条,就多了10条了,这个主要是left join导致的
roomguid ....
1 壹号s_order
roomguid ....
1 a
1 b这样结果就是
1 壹号 1 a
1 壹号 1 b多出来数据有什么奇怪么?
;with a
as
(
select 1 roomguid,'壹号' pname
),s_order
as
(
select 1 roomguid,'aa' vv
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
union all select 1 ,'aa'
)select *
from a
left join s_order o
on o.roomguid = a.roomguid
where a.pname = '壹号'
/*11条数据
roomguid pname roomguid vv
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
1 壹号 1 aa
*/
create table test(roomguid nvarchar(20),pname nvarchar(20))
insert into test select 'aaaaa','壹号'
insert into test select 'bbbbb','壹号'
create table s_order(roomguid nvarchar(20))
insert into s_order select 'aaaaa'
insert into s_order select 'aaaaa'
go
SELECT * FROM test A
left join s_order o
on a.RoomGUID=o.roomguid
WHERE pname='壹号'
/*
roomguid pname roomguid
-------------------- -------------------- --------------------
aaaaa 壹号 aaaaa
aaaaa 壹号 aaaaa
bbbbb 壹号 NULL(3 行受影响)
*/
go
drop table test,s_order
select *
from a
left join s_order o
on o.roomguid = a.roomguid
where a.pname = '壹号'就是11条,就多了10条了,这个主要是left join导致的