问问题呀

[starboys830623]

我有两个表
表1：college
结构是 id,name,state
1 aaa 1
2 bbb 1
3 ccc 1
表2: Science
结构是 id,cid,name,state
1 1 ddd 1
2 1 ggg 1
3 3 eee 1
4 3 fff 1
我想要的结果是

aaa
ddd
ggg
ccc
eee
fff
请问这个语句怎么写？谢谢了

[htl258_Tony]

老D，来看看这个

[dawugui]

create table tb1(id int,name varchar(10),state int) 
insert into tb1 values(1, 'aaa' , 1) 
insert into tb1 values(2, 'bbb' , 1) 
insert into tb1 values(3, 'ccc' , 1) 
create table tb2(id int,cid int,name varchar(10),state int)
insert into tb2 values(1 , 1 , 'ddd' , 1 )
insert into tb2 values(2 , 1 , 'ggg' , 1 )
insert into tb2 values(3 , 3 , 'eee' , 1 )
insert into tb2 values(4 , 3 , 'fff' , 1 )
go

select name1 tb1_name,name3 tb2_name from
(
select m.name name1 , m.name name2 ,name3 = '', px = 1 from tb1 m where id in (select cid from tb2)
union all
select name1 = '', m.name name2 , n.name name2, px = 2 from tb1 m , tb2 n where m.id = n.cid
) t
order by name2 , px

drop table tb1,tb2

/*
tb1_name   tb2_name   
---------- ---------- 
aaa        
           ddd
           ggg
ccc        
           eee
           fff

（所影响的行数为 6 行）


*/