各位大侠，帮帮忙，发了几天的贴。实在是急。谢谢

Question

有两个表如下：
产品基本资料表：
产品编号    产品名称     商品编号      消费国     单位      单价
1             AA          85131010       USA       PCS      10.18
2             BB          85131010       HONGKONG   PCS     9.15
3             CC          85121010       JAN        PCS     11.23
合同产品表：
合同号   产品编号   件料编号   件料名称     损耗      单耗
   38      1          1          A1           2       0.01
   38      1          2          A2           0       0.02
   38      1          3          A3           2       0.05
   38      1          10         A5           2       0.02
   38      2           1         A1           2       0.03
   38      2           8         A4           0       0.05
   38      2           3         A3           2       0.02
   38      2           5         A6           0       0.01
   38      3           2         A2           0       0.03
   38      3           5         A6           0       0.1
   38      3           10        A5           2       0.05
   38      3           8         A4           0       0.04
   38      3           3         A3           2       0.08现在要做一个查询，结果如下：
                       产品编号（1）        产品编号（2）        产品编号（3）  
件料编号   件料名称     单耗   损耗         单耗   损耗          单耗   损耗
1            A1           0.01    2          0.03     2           
2            A2           0.02    0                               0.03   0
3            A3           0.05    2          0.02     2           0.08   2
5            A6                               0.01    0           0.1    0
8            A4                               0.05    0           0.04   0     
10           A5           0.02    2                               0.05   2

Answer 1

如果产品编号只有1,2,3的话可以用如下语句试试:select a.件料编号,a.件料名称,b.单耗,b.损耗,c.单耗,c.损耗,d.单耗,d.损耗 
from 
(select 件料编号,件料名称 from 合同产品表   group by 件料编号,件料名称) as a
left join 合同产品表 b
on (a.件料编号=b.件料编号 and b.产品编号=1)
left join 合同产品表 c
on (a.件料编号=c.件料编号 and c.产品编号=2)
left join 合同产品表 d
on (a.件料编号=d.件料编号 and d.产品编号=3)

Answer 2

补充:  如果产品号是变化的,用存储过程生成SQL语句