表1 a
aId,aName
1 a1
2 a2
3 a3
表2 b
bId, bTitle
1 b1
2 b2
3 b3
4 b4我要得到的查询结果是:
aId,aName, bId,bTitle
1 a1 1 b1
1 a1 2 b2
1 a1 3 b3
1 a1 4 b4
2 a2 1 b1
2 a2 2 b2
2 a2 3 b3
2 a2 4 b4
3 a3 1 b1
3 a3 2 b2
3 a3 3 b3
3 a3 4 b4
请教高手,谢谢!
aId,aName
1 a1
2 a2
3 a3
表2 b
bId, bTitle
1 b1
2 b2
3 b3
4 b4我要得到的查询结果是:
aId,aName, bId,bTitle
1 a1 1 b1
1 a1 2 b2
1 a1 3 b3
1 a1 4 b4
2 a2 1 b1
2 a2 2 b2
2 a2 3 b3
2 a2 4 b4
3 a3 1 b1
3 a3 2 b2
3 a3 3 b3
3 a3 4 b4
请教高手,谢谢!
--> 测试数据: @表1
declare @表1 table (aId int,aName varchar(2))
insert into @表1
select 1,'a1' union all
select 2,'a2' union all
select 3,'a3'--> 测试数据: @表2
declare @表2 table (bId int,bTitle varchar(2))
insert into @表2
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3' union all
select 4,'b4'select * from @表1 cross join @表2 order by 1
/*
aId aName bId bTitle
----------- ----- ----------- ------
1 a1 1 b1
1 a1 2 b2
1 a1 3 b3
1 a1 4 b4
2 a2 1 b1
2 a2 2 b2
2 a2 3 b3
2 a2 4 b4
3 a3 1 b1
3 a3 2 b2
3 a3 3 b3
3 a3 4 b4
*/
select a.* , b.* from a , b
select a.* , b.* from a cross join b
select a.* , b.* from a cross join b order by a.aid , a.name , b.bid