导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

急求两表合并汇总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

...全文
702 点赞 收藏 12
写回复
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 行)
*/
回复
chen23520 2008-01-09
消耗是原料的投入数减去对应产品的产量,看excel可能清楚点,帖子中的列没对齐
回复
dobear_0922 2008-01-09
顶1楼,关注
回复
dawugui 2008-01-09
消耗怎么来的?没看到相关数据.
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告