sql怎么写？

[rongxh]

table a:
col1 col2 col3
CON1 A A1
CON2 A A1
CON3 A A2

TABLE B:
col1 col2
A A1
A A2
A A3

结果：

col1 col2 col3
CON1 A A1
CON1 A A2
CON1 A A3
CON2 A A1
CON2 A A2
CON2 A A3
CON3 A A1
CON3 A A2
CON3 A A3

[中国风]

select a.* from a,b

[中国风]

declare @a table(col1 varchar(5), col2 varchar(5), col3 varchar(5))
insert @a select 'CON1', 'A', 'A1'
union all select 'CON2', 'A', 'A1'
union all select 'CON3', 'A', 'A2'

declare @b TABLE(col1 varchar(5), col2 varchar(5))
insert @b select 'A', 'A1'
union all select 'A', 'A2'
union all select 'A', 'A3'

select a.col1,a.col2,[col3]=b.col2 from @a a,@b b order by col1


(3 行受影响)

(3 行受影响)
col1 col2 col3
----- ----- -----
CON1 A A1
CON1 A A2
CON1 A A3
CON2 A A1
CON2 A A2
CON2 A A3
CON3 A A1
CON3 A A2
CON3 A A3

(9 行受影响)

[中国风]

declare @a table(col1 varchar(5), col2 varchar(5), col3 varchar(5))
insert @a select 'CON1', 'A', 'A1'
union all select 'CON2', 'A', 'A1'
union all select 'CON3', 'A', 'A2'

declare @b TABLE(col1 varchar(5), col2 varchar(5))
insert @b select 'A', 'A1'
union all select 'A', 'A2'
union all select 'A', 'A3'

select a.col1,a.col2,[col3]=b.col2 from @a a cross join @b b order by col1


(3 行受影响)

(3 行受影响)
col1 col2 col3
----- ----- -----
CON1 A A1
CON1 A A2
CON1 A A3
CON2 A A1
CON2 A A2
CON2 A A3
CON3 A A1
CON3 A A2
CON3 A A3

(9 行受影响)

[dawugui]

if object_id('pubs..A') is not null
drop table A
go

create table A(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert into A(col1,col2,col3) values('CON1', 'A', 'A1')
insert into A(col1,col2,col3) values('CON2', 'A', 'A1')
insert into A(col1,col2,col3) values('CON3', 'A', 'A2')
go

if object_id('pubs..B') is not null
drop table B
go

create table B(col1 varchar(10),col2 varchar(10))
insert into B(col1,col2) values('A', 'A1')
insert into B(col1,col2) values('A', 'A2')
insert into B(col1,col2) values('A', 'A3')
go

select A.col1,A.col2,B.col2 from A,B where A.col2 = B.col1 order by A.col1

drop table A,B
/*
col1 col2 col2
---------- ---------- ----------
CON1 A A1
CON1 A A2
CON1 A A3
CON2 A A1
CON2 A A2
CON2 A A3
CON3 A A1
CON3 A A2
CON3 A A3

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