有A,B两张表
A表 B表
ID Type ID Name
1 2 1 aaa
2 2 2 bbb
3 1 3 ccc
4 1 4 ddd
5 1 5 eee
6 2 6 fff
7 1 7 ggg
8 2 8 hhh
9 2 9 iii怎么写一句SQL把TYPE=2的ID和NAME都取出来?
A表 B表
ID Type ID Name
1 2 1 aaa
2 2 2 bbb
3 1 3 ccc
4 1 4 ddd
5 1 5 eee
6 2 6 fff
7 1 7 ggg
8 2 8 hhh
9 2 9 iii怎么写一句SQL把TYPE=2的ID和NAME都取出来?
from a,b
where a.id = b.id
and a.type= 2
from a,b
where a.id = b.id
and a.type= 2
go
create table [A]([ID] int,[Type] int)
insert [A]
select 1,2 union all
select 2,2 union all
select 3,1 union all
select 4,1 union all
select 5,1 union all
select 6,2 union all
select 7,1 union all
select 8,2 union all
select 9,2
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[Name] varchar(3))
insert [B]
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' union all
select 4,'ddd' union all
select 5,'eee' union all
select 6,'fff' union all
select 7,'ggg' union all
select 8,'hhh' union all
select 9,'iii'
select a.id,b.name
from a
join b on a.id = b.id
where a.type= 2/**
id name
----------- ----
1 aaa
2 bbb
6 fff
8 hhh
9 iii(5 行受影响)
**/
select * from b
where id in (select id from A WHERE TYPE=2)
select a.id,b.name
from a join b
on a.id = b.id and a.type = 2
select a.id,b.name
from a
join b on a.id = b.id
where a.type= 2