急求两表合并汇总sql

chen23520 2008-01-09 03:42:40

有一个比较复杂的两表汇总查询问题：

表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

关键是如何用多种原料来对应一种产品，同时以产品产量减去原料投入之和得出消耗数量，因为数据量较大，希望优化查询性能，请各位老大指教！
附上excel

...全文

733 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

damo_baby 2008-01-11

打赏
举报

11楼的不错，就是这样

冷箫轻笛 2008-01-10

打赏
举报

乌龟太牛了。。。。

sp4 2008-01-10

打赏
举报

晕死，怎么都这么长的题目啊

dawugui 2008-01-10

打赏
举报

因为你有一行数据是显示为空格,所以要转换一下.

不然,就是显示为0,你可以自己试试.

int 转换为varchar是为了让结果为0的不显示吗？
--
对.

pigengler 2008-01-10

打赏
举报

太长了，顶一下

chen23520 2008-01-10

打赏
举报

int 转换为varchar是为了让结果为0的不显示吗？

dawugui 2008-01-10

打赏
举报

--今日有事,现在才有空,不好意思.

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 end



drop 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 行）

*/

chen23520 2008-01-09

打赏
举报

to:dawugui
产品数量不对呀！1#工序是2种原料对应一种产品，是想让产品只显示一行，您给出的重复了.

dawugui 2008-01-09

打赏
举报

--还有点错误,测试中.

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 end



drop 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 行）

*/