Cross Join 交叉结合三个表以上怎么写

[andrew064]

请给几个实例， 谢谢

另外，cross join与left join 同时使用又怎么写呢？同样是多个表，

[叶子]

declare @a table (id int)
insert into @a
select 1 union all
select 2 union all
select 3

declare @b table (id int)
insert into @b
select 2 union all
select 3 union all
select 4

declare @c table (id int)
insert into @c
select 1 union all
select 2 union all
select 4

select * from @a a 
left join @b b on a.id=b.id
left join @c c on a.id=c.id
/*
id          id          id
----------- ----------- -----------
1           NULL        1
2           2           2
3           3           NULL
*/

select * from @a a 
cross join @b b cross join @c c 
/*
id          id          id
----------- ----------- -----------
1           2           1
1           2           2
1           2           4
2           2           1
2           2           2
2           2           4
3           2           1
3           2           2
3           2           4
1           3           1
1           3           2
1           3           4
2           3           1
2           3           2
2           3           4
3           3           1
3           3           2
3           3           4
1           4           1
1           4           2
1           4           4
2           4           1
2           4           2
2           4           4
3           4           1
3           4           2
3           4           4
*/

[叶子]

declare @a table (id int)
insert into @a
select 1 union all
select 2 union all
select 3

declare @b table (id int)
insert into @b
select 2 union all
select 3 union all
select 4

declare @c table (id int)
insert into @c
select 1 union all
select 2 union all
select 4

select * from @a a 
left join @b b on a.id=b.id
cross join @c c

/*
id          id          id
----------- ----------- -----------
1           NULL        1
1           NULL        2
1           NULL        4
2           2           1
2           2           2
2           2           4
3           3           1
3           3           2
3           3           4
*/

[勿勿]

楼上的例子很实际。

[andrew064]

select * from @a a
left join @b b on a.id=b.id
cross join @c c

可以这样子吗?

select * from @a a 
left join @b b on a.id=b.id and a.其它字段='xxx'
cross join @c c

where c.其它字段='yyyyy'

[tiger_dxc]

declare @a table (id int)
insert into @a
select 1 union all
select 2 union all
select 3

declare @b table (id int)
insert into @b
select 2 union all
select 3 union all
select 4

declare @c table (id int)
insert into @c
select 1 union all
select 2 union all
select 4

select * from @a a
left join @b b on a.id=b.id
cross join @c c

[苦苦的潜行者]

select * from a,b,c