22,209
社区成员
发帖
与我相关
我的任务
分享
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
;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
*/