求一联表查询的SQL语句

[ahhisoft]

有A表、B表、C表

A表：
Main_ID Introduce
1 abc
2 bdc

B表：
Obj_ID Introduce Main_Id
1 abc 1
2 bdc 1
3 ddf 1

C表：
ID Obj_Id Level
1 1 5
2 1 6

现在的要求是通过A表的Introduce字段定位B表的记录集的Obj_Id，再在C表中检查对应的Obj_Id的记录level值大于5的记录总数。

[wfliu]

select count(c.id) from a,b,c where a.Introduce = 'abc' and (a.Introduce = b.Introduce and b.Obj_ID = c.Obj_ID)

[zlp321002]

select
Introduce=A.Introduce,
Level=count(Level)
from A表 A inner join B表 B
on A.Introduce=B.Introduce
inner join C表 C
on B.Obj_ID=C.Obj_Id
and C.Level>5
group by A.Introduce
Order by A.Introduce

[zlp321002]

--测试环境
Create table A表(Main_ID int,Introduce varchar(10))
insert into A表 select 1,'abc'
union all select 2,'bdc'


Create table B表(Obj_ID int,Introduce varchar(10),Main_Id int)
insert into B表 select 1,'abc',1
union all select 2,'bdc',1
union all select 3,'ddf',1

Create table C表(ID int,Obj_Id int,Level int)
insert into C表 select 1,1,5
union all select 2,1,6

--查询
select
Introduce=A.Introduce,
Level=count(Level)
from A表 A inner join B表 B
on A.Introduce=B.Introduce
inner join C表 C
on B.Obj_ID=C.Obj_Id
and C.Level>5
group by A.Introduce
Order by A.Introduce

--结果

Introduce Level
---------- -----------
abc 1

（所影响的行数为 1 行）

--删除环境
Drop table 表A,表B,表C

[Well]

用關聯就是行了

[zhaoanle]

select * from a,b,c
where a.introduce=b.introduce
and b.obj_id=c.obj_id
and c.level>5

[bfdhh]

create table a (Main_ID varchar(200),Introduce varchar(200))
create table b (Obj_ID varchar(200),Introduce varchar(200),Main_ID varchar(200))
create table c (ID varchar(200),Obj_ID varchar(200),level varchar(200))

insert a
select '1','abc' union all
select '2','dbc'

insert b
select '1','abc','1' union all
select '2','dbc','1' union all
select '3','ddf','1'

insert c
select '1','1','5' union all
select '2','1','6'

select
count(c.level) as number
from (select * from c where level>='5') c where obj_ID in (select b.obj_id from (select * From a where Introduce='abc') a
join b on a.main_ID=b.main_ID)

[---涛声依旧---]

select a.Introduce,count(1) as number
from A表 a inner join B表 on a.Introduce=b.Introduce
inner join C表 c on b.Obj_Id=c.Obj_Id
where Level>5
group by a.Introduce

[wcy1978]

select count(c.id)
from a,b,c
where a.introduce=b.introduce and b.obj_id=c.obj_id and c.level>5