紧急求助：高效率两表合并查询

Question

有一个比较复杂的两表汇总查询问题：表1：原料表：其中第一行为含义注释，第二行为列名，期初+领用-投料=期末
工序编号    工序名称    原料编号   原料规格   期初数    领用数    投料数     期末数   对应产品编号
  GXBH      GXMC      YLBH      YLGG     QCSL     LYSL     TRSL     SLYE    CPBH
   01        1#工序      M01       70S      100      300      350       50     P01
   01       1#工序     M02       80S       20       200      210      10      P01
   02       2#工序     M03       75S       10       100      100      10      P02
   02       2#工序     M03       75S        0       200      100     100      P03  表2：产品表：其中工序编号、工序名称、产品编号同原料表
工序编号    工序名称    产品编号   产品规格   产量    
  GXBH      GXMC      CPBH      CPGG    CPSL
  01        1#工序     P01       P30S    550
  02        2#工序     P02       P28S     90
  02        2#工序     P03       P26S    100根据以上两表汇总出以下报表：其中消耗数量=每种产品对应的原料投入（可能多种原料）之和-本产品产量工序编号  工序名称  原料编号  原料规格  期初数   领用数   投料数   期末数  产品编号  产品规格  产品产量  消耗数量
  01     1#工序     M01      70S     100      300    350      50     P01     P30S     550      10
  01     1#工序     M02      80S      20      200    210      10    
   1#工序小计                           120      500    560      60                      550      10 
  02     2#工序     M03      75S      10      100    100      10      P02    P28S      90      10
  02     2#工序     M03      75S       0      200    100     100      P03    P26S     100       0
   2#工序小计                             10      300    200     110                      190      10关键是如何用多种原料来对应一种产品，同时以产品产量减去原料投入之和得出消耗数量，因为数据量较大，希望优化查询性能，请各位老大指教！

Answer 1

select 工序编号,工序名称,原料编号,原料规格,sum(........)
from table 
group by 工序编号,工序名称,原料编号,原料规格 with rollup

Answer 2

楼主
工序编号     工序名称     原料编号     原料规格     期初数       领用数       投料数       期末数     产品编号     产品规格     产品产量     消耗数量 
    01           1#工序           M01             70S           100             300         350             50           P01           P30S           550             10 
    01           1#工序           M02             80S             20             200         210             10         
      1#工序小计                                                       120             500         560             60                                             550             10   
    02           2#工序           M03             75S             10             100         100             10             P02         P28S             90             10 
    02           2#工序           M03             75S               0             200         100           100             P03         P26S           100               0 
      2#工序小计                                                           10             300         200           110                                             190             10 
结果是怎么得到的

Answer 3

to:dawugui:
消耗是原料的投入数减去对应产品的产量,帖子中的列没对齐,在http://topic.csdn.net/u/20080109/15/34d3098a-4458-4945-a8f7-e8ef537348f7.html?seed=272920354中有附件excel

Answer 4

--还有点错误,测试中.
create table tb1(GXBH varchar(10), GXMC varchar(10), YLBH varchar(10), YLGG varchar(10), QCSL int, LYSL int, TRSL int,SLYE int,CPBH varchar(10))
insert into tb1 values('01', '1#工序', 'M01', '70S', 100, 300, 350, 50 , 'P01') 
insert into tb1 values('01', '1#工序', 'M02', '80S', 20 , 200, 210, 10 , 'P01') 
insert into tb1 values('02', '2#工序', 'M03', '75S', 10 , 100, 100, 10 , 'P02')
insert into tb1 values('02', '2#工序', 'M03', '75S', 0 , 200, 100, 100, 'P03')
create table tb2(GXBH varchar(10), GXMC varchar(10), CPBH varchar(10), CPGG varchar(10), CPSL int)
insert into tb2 values('01', '1#工序', 'P01', 'P30S', 550 )
insert into tb2 values('02', '2#工序', 'P02', 'P28S', 90 )
insert into tb2 values('02', '2#工序', 'P03', 'P26S', 100 )
go
select * from
(
select m.GXBH , m.GXMC , m.YLBH , m.YLGG , m.QCSL , m.LYSL , m.TRSL , m.SLYE , m.CPBH , n.CPGG,n.CPSL , XHSL = o.TRSL - n.CPSL
from tb1 m,
(select CPBH , CPGG , CPSL = sum(CPSL) from tb2 group by CPBH , CPGG) n,
(select CPBH , TRSL = sum(TRSL) from tb1 group by CPBH ) o
where m.cpbh = n.cpbh and m.cpbh = o.cpbh
union all
select m.GXBH , GXMC = '小计', YLBH='' , YLGG='' , QCSL = sum(m.QCSL) , LYSL = sum(m.LYSL) , TRSL =sum(m.TRSL) , SLYE=sum(m.SLYE) , CPBH ='', CPGG='',CPSL = sum(n.CPSL) , XHSL = sum(o.TRSL - n.CPSL)
from tb1 m,
(select CPBH , CPGG , CPSL = sum(CPSL) from tb2 group by CPBH , CPGG) n,
(select CPBH , TRSL = sum(TRSL) from tb1 group by CPBH ) o
where m.cpbh = n.cpbh and m.cpbh = o.cpbh
group by m.gxbh 
union all
select GXBH = '合计', GXMC = '', YLBH='' , YLGG='' , QCSL = sum(m.QCSL) , LYSL = sum(m.LYSL) , TRSL =sum(m.TRSL) , SLYE=sum(m.SLYE) , CPBH ='', CPGG='',CPSL = sum(n.CPSL) , XHSL = sum(o.TRSL - n.CPSL)
from tb1 m,
(select CPBH , CPGG , CPSL = sum(CPSL) from tb2 group by CPBH , CPGG) n,
(select CPBH , TRSL = sum(TRSL) from tb1 group by CPBH ) o
where m.cpbh = n.cpbh and m.cpbh = o.cpbh
) t
order by gxbh , case GXMC when '小计' then 2 else 1 enddrop table tb1,tb2/*
GXBH       GXMC       YLBH       YLGG       QCSL        LYSL        TRSL        SLYE        CPBH       CPGG       CPSL        XHSL        
---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ---------- ---------- ----------- ----------- 
01         1#工序       M01        70S        100         300         350         50          P01        P30S       550         10
01         1#工序       M02        80S        20          200         210         10          P01        P30S       550         10
01         小计                               120         500         560         60                                1100        20
02         2#工序       M03        75S        10          100         100         10          P02        P28S       90          10
02         2#工序       M03        75S        0           200         100         100         P03        P26S       100         0
02         小计                               10          300         200         110                               190         10
合计                                          130         800         760         170                               1290        30（所影响的行数为 7 行）
*/

Answer 5

--今日有事,现在才有空,不好意思.
create table tb1(GXBH varchar(10), GXMC varchar(10), YLBH varchar(10), YLGG varchar(10), QCSL int, LYSL int, TRSL int,SLYE int,CPBH varchar(10))
insert into tb1 values('01', '1#工序', 'M01', '70S', 100, 300, 350, 50 , 'P01') 
insert into tb1 values('01', '1#工序', 'M02', '80S', 20 , 200, 210, 10 , 'P01') 
insert into tb1 values('02', '2#工序', 'M03', '75S', 10 , 100, 100, 10 , 'P02')
insert into tb1 values('02', '2#工序', 'M03', '75S', 0 , 200, 100, 100, 'P03')
create table tb2(GXBH varchar(10), GXMC varchar(10), CPBH varchar(10), CPGG varchar(10), CPSL int)
insert into tb2 values('01', '1#工序', 'P01', 'P30S', 550 )
insert into tb2 values('02', '2#工序', 'P02', 'P28S', 90 )
insert into tb2 values('02', '2#工序', 'P03', 'P26S', 100 )
go
select * from
(
  select m.GXBH , m.GXMC , m.YLBH , m.YLGG , m.QCSL , m.LYSL , m.TRSL , m.SLYE , 
  CPBH = case when YLBH=(select min(YLBH) from tb1 where CPBH=M.CPBH) then M.CPBH else '' end,
  CPGG = case when YLBH=(select min(YLBH) from tb1 where CPBH=M.CPBH) then N.CPGG else '' end,
  CPSL = case when YLBH=(select min(YLBH) from tb1 where CPBH=M.CPBH) then CAST(N.CPSL AS VARCHAR) else '' end,
  XHSL = case when YLBH=(select min(YLBH) from tb1 where CPBH=M.CPBH) then CAST(o.TRSL - n.CPSL AS VARCHAR) else '' end
  from tb1 m,
  (select CPBH , CPGG , CPSL = sum(CPSL) from tb2 group by CPBH , CPGG) n,
  (select CPBH , TRSL = sum(TRSL) from tb1 group by CPBH ) o
  where m.cpbh = n.cpbh and m.cpbh = o.cpbh
  union all
  select m.*,
  CPSL = case when t2.CPSL <> 0 then CAST(t2.CPSL AS VARCHAR) else '' end,
  XHSL = case when T1.TRSL - T2.CPSL <> 0 then CAST(T1.TRSL - T2.CPSL AS VARCHAR) else '' end from
  (select GXBH , GXMC = '小计', YLBH='' , YLGG='' , QCSL = sum(QCSL) , LYSL = sum(LYSL) , TRSL =sum(TRSL) , SLYE=sum(SLYE) , cpbh = '', CPGG='' from tb1 group by gxbh) m,
  (select GXBH , TRSL = sum(TRSL) FROM tb1 group by GXBH ) t1,
  (select GXBH , CPSL = sum(CPSL) FROM tb2 group by GXBH ) t2
  where m.GXBH = T1.GXBH AND M.GXBH = T2.GXBH
  union all
  select m.*,
  CPSL = cast((select sum(CPSL) FROM tb2) as varchar),
  XHSL = cast((select sum(TRSL) FROM tb1) - (select sum(CPSL) FROM tb2) as varchar)
  from
  (select GXBH = '合计', GXMC = '', YLBH='' , YLGG='' , QCSL = sum(QCSL) , LYSL = sum(LYSL) , TRSL =sum(TRSL) , SLYE=sum(SLYE) , CPBH ='', CPGG='' from tb1) m
) T
order by gxbh , case GXMC when '小计' then 2 else 1 enddrop table tb1,tb2/*
GXBH       GXMC       YLBH       YLGG       QCSL        LYSL        TRSL        SLYE        CPBH       CPGG       CPSL                           XHSL                           
---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ---------- ---------- ------------------------------ ------------------------------ 
01         1#工序       M01        70S        100         300         350         50          P01        P30S       550                            10
01         1#工序       M02        80S        20          200         210         10                                                               
01         小计                               120         500         560         60                                550                            10
02         2#工序       M03        75S        10          100         100         10          P02        P28S       90                             10
02         2#工序       M03        75S        0           200         100         100         P03        P26S       100                            0
02         小计                               10          300         200         110                               190                            10
合计                                          130         800         760         170                               740                            20（所影响的行数为 7 行）
*/