一个多表查询

Question

table1
  Company     Genre     Model      Total     
   IBM         IC       A2051       200
   IBM         PCB      12ad        300
   IBM         IC       ded22       220
   IBM         PCB      aed45       220  
   Lenovo      IC       adde2       500
   Lenovo      PCB       15a        100 
   Lenovo      IC        ad33        0
   ZIP         IC        abcd       200
   ZIP         IC        addf        0
------------------------------------------------------   
table2
  Company     PlanID    PlanNum     Complete
   IBM        09001      100          50       
   IBM        09002      200          170
   Lenovo     09003      80           80
-------------------------------------------------------
table3
   PlanID   Genre     Model      Total
   09001     IC       A2051       100
   09001     PCB      12ad        190
   09002     IC       ded22       200
   09002     PCB      aed45       200
   09003     IC       adde2       80
   09003     PCB      15a         80
-------------------------------------------------------
Result  TB1
   Company   PCB    Total11   total12    IC     Total21   Total22
    IBM                                 A2051     200      50
    IBM      12ad    300       140
    IBM                                 ded22     220      30 
    IBM      aed45   220       30
   Lenovo                               adde2     500      0
   Lenovo    15a     100       0
    ZIP                                 abcd      200说明：Table1里的Genre只能是PCB和IC，Result TB1里PCB列中填的是PCB里MODEL,IC列中填的是IC的Model,Total11和Total21为Table1里的数量。
如果Table2里的PlanNum小于Complete，则用Table3里的Total-Complete的值填入Total12和Total22.
                                       
   

Answer 1

table2和table3按PlanID联接table1和2，3联接后的表用company,model,genre联接

Answer 2

table1 
  Company    Genre    Model      Total    
  IBM        IC      A2051      200 
  IBM        PCB      12ad        300 
  IBM        IC      ded22      220 
  IBM        PCB      aed45      220  
  Lenovo      IC      adde2      500 
  Lenovo      PCB      15a        100 
  Lenovo      IC        ad33        0 
  ZIP        IC        abcd      200 
  ZIP        IC        addf        0 可以改成以下：
  Company    IC        Total        PCB      Total        
  IBM        A2051      200 
  IBM                              12ad        300 
  IBM        ded22      220 
  IBM                              aed45      220  
  Lenovo     adde2      500 
  Lenovo                            15a        100 
  Lenovo     ad33        0 
  ZIP        abcd      200 
  ZIP        addf        0 

Answer 3

declare @table table (company varchar(6),genre varchar(3),model varchar(5),total varchar(20))
insert into @table
select 'ibm','ic','a2051',200 union all
select 'ibm','pcb','12ad',300 union all
select 'ibm','ic','ded22',220 union all
select 'ibm','pcb','aed45',220 union all
select 'lenovo','ic','adde2',500 union all
select 'lenovo','pcb','15a',100 union all
select 'lenovo','ic','ad33',0 union all
select 'zip','ic','abcd',200 union all
select 'zip','ic','addf',0select * from @tableselect company ,ic=model, total=total,pcb='',total='' from @table where genre='ic'
union all
select company ,ic='', total='',pcb=model,total=total from @table where genre='pcb'
order by company

Answer 4

declare @table table (company varchar(6),genre varchar(3),model varchar(5),total varchar(20))
insert into @table
select 'ibm','ic','a2051',200 union all
select 'ibm','pcb','12ad',300 union all
select 'ibm','ic','ded22',220 union all
select 'ibm','pcb','aed45',220 union all
select 'lenovo','ic','adde2',500 union all
select 'lenovo','pcb','15a',100 union all
select 'lenovo','ic','ad33',0 union all
select 'zip','ic','abcd',200 union all
select 'zip','ic','addf',0select * from @table
/*
company genre model total
------- ----- ----- --------------------
ibm     ic    a2051 200
ibm     pcb   12ad  300
ibm     ic    ded22 220
ibm     pcb   aed45 220
lenovo  ic    adde2 500
lenovo  pcb   15a   100
lenovo  ic    ad33  0
zip     ic    abcd  200
zip     ic    addf  0
*/
select company ,ic=model, total=total,pcb='',total='' from @table where genre='ic'
union all
select company ,ic='', total='',pcb=model,total=total from @table where genre='pcb'
order by company/*
company ic    total                pcb   total
------- ----- -------------------- ----- --------------------
ibm     a2051 200                        
ibm     ded22 220                        
ibm                                12ad  300
ibm                                aed45 220
lenovo                             15a   100
lenovo  adde2 500                        
lenovo  ad33  0                          
zip     abcd  200                        
zip     addf  0   
*/

Answer 5

table1
 Company    IC        Total1        PCB      Total2        
  IBM        A2051      200 
  IBM                              12ad        300 
  IBM        ded22      220 
  IBM                              aed45      220  
  Lenovo    adde2      500 
  Lenovo                            15a        100 
  Lenovo    ad33        0 
  ZIP        abcd      200 
  ZIP        addf        0 
----------------------------------------------------------
table2 
 Company     PlanNum   Complete  IC        PCB       Total 
  IBM          100       50      A2051                100 
  IBM          100       50               12ad        190 
  IBM          200       170     ded22                200 
  IBM          200       170              aed45       200 
  Lenovo       80        80      adde2                80 
  Lenovo       80        80                15a        80 
------------------------------------------------------- 
Result  TB1 
  Company  PCB    Total11  total12    IC    Total21  Total22 
    IBM                                A2051    200      50 
    IBM      12ad    300      140 
    IBM                                ded22    220      30 
    IBM      aed45  220      30 
  Lenovo                              adde2    500      0 
  Lenovo    15a    100      0 
    ZIP                                abcd      200 
说明：table1和table2按cpmpany,IC,PCB联结，
如果Table2里的PlanNum小于Complete，则用Table2里的Total-Complete的值填入Total12和Total22.