34,594
社区成员
发帖
与我相关
我的任务
分享
create table a(AId int,AName varchar(10))
insert into a
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc'
create table B(BId int,BName varchar(10))
insert into b
select 1 ,'aa' union all
select 2 ,'bb' union all
select 3 ,'cc'
create table C(AId int,BId int)
insert into c
select 1,1 union all
select 1,2
select *
from
(
select *
from a
where a.aid = 1
)a
cross join
(
select b.*
from b
where b.bid not in (select bid from c where aid = 1)
)b
/*
AId AName BId BName
1 aa 3 cc
*/
create table a(AId int,AName varchar(10))
insert into a
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc'
create table B(BId int,BName varchar(10))
insert into b
select 1 ,'aa' union all
select 2 ,'bb' union all
select 3 ,'cc'
create table C(AId int,BId int)
insert into c
select 1,1 union all
select 1,2
select *
from
(
select *
from a
where a.aid = 1
)a
cross join
(
select b.*
from b
where b.bid not in (select bid from c where aid = 1)
)b
/*
AId AName BId BName
1 aa 3 cc
*/
[/quote]
你好:语句中(
select *
from a
where a.aid = 1
)起到一个什么作用?[/quote]
其实也没什么作用,因为看到你要的结果中,你是需要a表中的aid=1的值,
因为这个aid = 1的值,和后面的bid值,之间没有关系,所以后面还用了一个cross join,这样就不需要关联条件了。
然后后面的b表,过滤了aid=1的bid,然后2个结果集合并成一条数据create table a(AId int,AName varchar(10))
insert into a
select 1, 'aa' union all
select 2, 'bb' union all
select 3, 'cc'
create table B(BId int,BName varchar(10))
insert into b
select 1 ,'aa' union all
select 2 ,'bb' union all
select 3 ,'cc'
create table C(AId int,BId int)
insert into c
select 1,1 union all
select 1,2
select *
from
(
select *
from a
where a.aid = 1
)a
cross join
(
select b.*
from b
where b.bid not in (select bid from c where aid = 1)
)b
/*
AId AName BId BName
1 aa 3 cc
*/
[/quote]
你好:语句中(
select *
from a
where a.aid = 1
)起到一个什么作用?