一个非常复杂的三表查询（select）语句

[mailto520]

table1
company genre model total
NOKIA IC A1820 500
NOKIA PCB B1850 600
NOKIA IC A1970 300
IBM IC Z8850 100
IBM IC B5520 0
IBM IC E0055 0
-------------------------------------------------
table2
Company planID praxis
NOKIA 0812001 400
IBM 0812002 300
--------------------------------------------------
table3
PlanID Genre Model Total
0812001 IC A1820 500
0812001 PCB B1850 480
0812002 IC A1970 300
0812002 IC Z8850 310
0812002 IC E0055 320
----------------------------------------------------

查询结果:
company Genre Model total total2
NOKIA IC A1820 500 100
NOKIA PCB B1850 600 80
NOKIA IC A1970 300 0
IBM IC Z8850 100 10
IBM IC E0055 0 20

说明：
将table2和table3按planID联接，用table3.total-table2.praxis=total2 得到下表
Company PlanID Genre Model Total2
NOKIA 0812001 IC A1820 100
NOKIA 0812001 PCB B1850 80
IBM 0812002 IC A1970 0
IBM 0812002 IC Z8850 10
IBM 0812002 IC E0055 20

然后将得到的表和table1按company,genre,model联接

[dawugui]

select b.company , c.Genre , c.Model , c.total - b.praxis total2 from a , b , c where b.planID = c.planID and b.company = a.company and c.genre = a.genre and c.model = a.model

[ljhcy99]

select A.Company,A.Genre,A.Model, table1.total,A. total2
from table1,
(select table3.PlanID ,table2.Company ,table3.Genre, table3.Model,table3.total-table2.praxis as total2
from table3,table2
where table3.planID=table2.planID
) AS　A
where table1.company = A..company
and table1.genre = a.table1.genre
and table1.model =A.table1.model

[dawugui]

create table a(company  varchar(10),    genre varchar(10),   model varchar(10),     total int) 
insert into a values('NOKIA' ,       'IC'  ,    'A1820' ,       500 )
insert into a values('NOKIA' ,       'PCB' ,    'B1850' ,       600 )
insert into a values('NOKIA' ,       'IC'  ,    'A1970' ,       300 )
insert into a values('IBM'   ,       'IC'  ,    'Z8850' ,       100 )
insert into a values('IBM'   ,       'IC'  ,    'B5520' ,       0 )
insert into a values('IBM'   ,       'IC'  ,    'E0055' ,       0 )
create table b(Company varchar(10) ,   planID varchar(10) ,   praxis int)
insert into b values('NOKIA' ,     '0812001' , 400 )
insert into b values('IBM'   ,     '0812002' , 300 )
create table c(PlanID varchar(10) ,   Genre varchar(10) , Model varchar(10) , Total int)
insert into c values('0812001' ,   'IC'  ,  'A1820' , 500 )
insert into c values('0812001' ,   'PCB' ,  'B1850' , 480 )
insert into c values('0812002' ,   'IC'  ,  'A1970' , 300 )
insert into c values('0812002' ,   'IC'  ,  'Z8850' , 310 )
insert into c values('0812002' ,   'IC'  ,  'E0055' , 320  )
select b.company , c.Genre , c.Model , c.total , c.total - b.praxis total2 from a , b , c where b.planID = c.planID and b.company = a.company and c.genre = a.genre and c.model = a.model

drop table a, b, c

/*
company    Genre      Model      total       total2      
---------- ---------- ---------- ----------- ----------- 
NOKIA      IC         A1820      500         100
NOKIA      PCB        B1850      480         80
IBM        IC         Z8850      310         10
IBM        IC         E0055      320         20

（所影响的行数为 4 行）
*/

[mailto520]

谢谢dawugui

但不能显示table1里的所有记录，table1里的记录没有联接，就不能显示，如：
company genre model total
NOKIA IC A1820 500
NOKIA PCB B1850 600
NOKIA IC A1970 300
IBM IC Z8850 100
IBM IC B5520 0
IBM IC B5521 10 IBM IC E0055 0

B5521我需要显示，B5520则不显示，
如果total和total2都为0时，不显示，如果有一个不为0时，则显示

[dawugui]

create table a(company  varchar(10),    genre varchar(10),   model varchar(10),     total int) 
insert into a values('NOKIA' ,       'IC'  ,    'A1820' ,       500 )
insert into a values('NOKIA' ,       'PCB' ,    'B1850' ,       600 )
insert into a values('NOKIA' ,       'IC'  ,    'A1970' ,       300 )
insert into a values('IBM'   ,       'IC'  ,    'Z8850' ,       100 )
insert into a values('IBM'   ,       'IC'  ,    'B5520' ,       0 )
insert into a values('IBM'   ,       'IC'  ,    'E0055' ,       0 )
create table b(Company varchar(10) ,   planID varchar(10) ,   praxis int)
insert into b values('NOKIA' ,     '0812001' , 400 )
insert into b values('IBM'   ,     '0812002' , 300 )
create table c(PlanID varchar(10) ,   Genre varchar(10) , Model varchar(10) , Total int)
insert into c values('0812001' ,   'IC'  ,  'A1820' , 500 )
insert into c values('0812001' ,   'PCB' ,  'B1850' , 480 )
insert into c values('0812002' ,   'IC'  ,  'A1970' , 300 )
insert into c values('0812002' ,   'IC'  ,  'Z8850' , 310 )
insert into c values('0812002' ,   'IC'  ,  'E0055' , 320  )

select * from 
(
select a.company , a.genre , a.model , a.total , isnull(m.total2 , 0) total2 from a left join
(select b.company , c.Genre , c.Model , c.total , c.total - b.praxis total2 from b , c where b.planID = c.planID) m
on a.company = m.company and a.genre = m.genre and a.model = m.model
) n
where total <> 0 or total2 <> 0
drop table a, b, c

/*
company    genre      model      total       total2      
---------- ---------- ---------- ----------- ----------- 
NOKIA      IC         A1820      500         100
NOKIA      PCB        B1850      600         80
NOKIA      IC         A1970      300         0
IBM        IC         Z8850      100         10
IBM        IC         E0055      0           20

（所影响的行数为 5 行）
*/