select
isnull (a.日期,b.日期) as 日期,
isnull (a.线别,b.线别) as 线别,
isnull (a.料号,null) as 料号,
isnull (a.数量) as 数量,
isnull (b.成品号, null ) as 成品号,
isnull (b.数量) as 数量
from 投入表 a full join 产出表 b
on a.日期=b.日期 and a.线别=b.线别
isnull (a.日期,b.日期) as 日期,
isnull (a.线别,b.线别) as 线别,
isnull (a.料号,null) as 料号,
isnull (a.数量) as 数量,
isnull (b.成品号, null ) as 成品号,
isnull (b.数量) as 数量
from 投入表 a full join 产出表 b
on a.日期=b.日期 and a.线别=b.线别
isnull (a.日期,b.日期) as 日期,
isnull (a.线别,b.线别) as 线别,
isnull (a.料号,null) as 料号,
isnull (a.数量,null) as 数量,
isnull (b.成品号, null ) as 成品号,
isnull (b.数量,null) as 数量
from 投入表 a full join 产出表 b
on a.日期=b.日期 and a.线别=b.线别
where a.线别 = b.线别
出来的表基本上就是你要求的数据了,稍微调整一下即可。
各线别分开,同线别同料号的数量合计
(select a.日期,a.线别,a.料号,a.数量,b.成品号,b.数量
from a
left outer join b
on a.日期=b.日期 and a.线别=b.线别
union
select b.日期,b.线别,a.料号,a.数量,b.成品号,b.数量
from b
left outer join a
on a.日期=b.日期 and a.线别=b.线别) as t
select id=identity(int,1,1),*
into #t0
from a
order by 日期,线别select id=identity(int,1,1),*
into #t1
from b
order by 日期,线别select isnull(a.日期,b.日期),
isnull(a.线别,b.线别),
a.料号,
a.数量,
b.成品号,
b.数量
from ( select *,d=id-( select min(id)
from #t0
where 日期=c.日期 and 线别=c.线别
)
from #t0 c
) a
full join
( select *,d=id-( select min(id)
from #t1
where 日期=d.日期 and 线别=d.线别
)
from #t1 d
) b
on a.日期=b.日期 and a.线别=b.线别 and a.d=b.ddrop table #t0
drop table #t1
drop table a
drop table b
GOcreate table a(date varchar(10),line varchar(10),partID varchar(10),qty int)
create table b(date varchar(10),line varchar(10),prodID varchar(10),qty int)
insert into a
select '06/15/2004', '06-01', 'A001', 100.00 union all
select '06/15/2004', '06-01', 'A002', 100.00 union all
select '06/15/2004', '06-01', 'A003', 100.00 union all
select '06/17/2004', '06-02', 'A001', 100.00 union all
select '06/17/2004', '06-03', 'A001', 100.00
insert into b
select '06/15/2004', '06-01', 'AAA', 50.00 union all
select '06/15/2004', '06-01', 'BBB', 50.00 union all
select '06/16/2004', '06-02', 'AAA', 50.00 union all
select '06/16/2004', '06-03', 'BBB', 50.00 union all
select '06/17/2004', '06-03', 'AAA', 50.00
--yesterday2000(火焱)
select
isnull (a.date,b.date) as 日期,
isnull (a.line,b.line) as 线别,
isnull (a.partID,null) as 料号,
isnull (a.qty,null) as 数量,
isnull (b.prodId, null ) as 成品号,
isnull (b.qty,null) as 数量
from a full join b
on a.date=b.date and a.line=b.line--windindance(风舞轻扬)
select * from a,b
where a.line = b.line
order by a.date
select distinct * from
(select a.date,a.line,a.partID,a.qty as aQty,b.prodID,b.qty as bQty
from a
left outer join b
on a.date=b.date and a.line=b.line
union
select b.date,b.line,a.partID,a.qty,b.prodID,b.qty --这的b.line由b.date改成
from b
left outer join a
on a.date=b.date and a.line=b.line) as t改动了 huwgao(小楼听雨)一些,不知是否原意,现时结果也不对
,线别=isnull(a.line,b.line)
,料号=isnull(a.partID,''),数量=isnull(a.qty,0)
,成品号=isnull(b.prodID,''),数量=isnull(b.qty,0)
from(
select date,line,partID,qty=sum(qty)
from a
group by date,line,partID
)a full join(
select date,line,prodID,qty=sum(qty)
from b
group by date,line,prodID
)b on a.date=b.date and a.line=b.line
and (select count(distinct partID) from a aa
where aa.line=a.line and aa.date=a.date and partID<=a.partID
)=(select count(distinct prodID) from b bb
where bb.line=b.line and bb.date=b.date and bb.prodID<=b.prodID)
order by 日期,线别
create table a(date varchar(10),line varchar(10),partID varchar(10),qty int)
create table b(date varchar(10),line varchar(10),prodID varchar(10),qty int)
insert into a
select '06/15/2004', '06-01', 'A001', 100.00 union all
select '06/15/2004', '06-01', 'A002', 100.00 union all
select '06/15/2004', '06-01', 'A003', 100.00 union all
select '06/17/2004', '06-02', 'A001', 100.00 union all
select '06/17/2004', '06-03', 'A001', 100.00 insert into b
select '06/15/2004', '06-01', 'AAA', 50.00 union all
select '06/15/2004', '06-01', 'BBB', 50.00 union all
select '06/16/2004', '06-02', 'AAA', 50.00 union all
select '06/16/2004', '06-03', 'BBB', 50.00 union all
select '06/17/2004', '06-03', 'AAA', 50.00
go--查询
select 日期=isnull(a.date,b.date)
,线别=isnull(a.line,b.line)
,料号=isnull(a.partID,''),数量=isnull(a.qty,0)
,成品号=isnull(b.prodID,''),数量=isnull(b.qty,0)
from(
select date,line,partID,qty=sum(qty)
from a
group by date,line,partID
)a full join(
select date,line,prodID,qty=sum(qty)
from b
group by date,line,prodID
)b on a.date=b.date and a.line=b.line
and (select count(distinct partID) from a aa
where aa.line=a.line and aa.date=a.date and partID<=a.partID
)=(select count(distinct prodID) from b bb
where bb.line=b.line and bb.date=b.date and bb.prodID<=b.prodID)
order by 日期,线别
go--删除测试
drop table a,b/*--测试结果日期 线别 料号 数量 成品号 数量
---------- ---------- ---------- ----------- ---------- -----------
06/15/2004 06-01 A001 100 AAA 50
06/15/2004 06-01 A002 100 BBB 50
06/15/2004 06-01 A003 100 0
06/16/2004 06-02 0 AAA 50
06/16/2004 06-03 0 BBB 50
06/17/2004 06-02 A001 100 0
06/17/2004 06-03 A001 100 AAA 50(所影响的行数为 7 行)
--*/
select id=identity(int,1,1),*
into #t0
from aorder by date,lineselect id=identity(int,1,1),*
into #t1
from b
order by date,line
select isnull(a.date,b.date) as date,
isnull(a.line,b.line) as line,
a.partID,
a.qty,
b.prodID,
b.qty
from ( select *,d=id-( select min(id)
from #t0
where date=c.date and line=c.line
)
from #t0 c
) a
full join
( select *,d=id-( select min(id)
from #t1
where date=d.date and line=d.line
)
from #t1 d
) b
on a.date=b.date and a.line=b.line and a.d=b.d
order by date,line
drop table #t0
drop table #t1victorycyz(中海)得到的明细数据是合乎要求的,先谢了!也感谢楼上各位的参与!另:还有更化的方案吗?由于数据量较大,会用日期段和线别做查询,最好设上什么索引呢?晚上结贴!
1、是否要进行统计,你现在用的victorycyz(中海)的方法是没有进行统计的。
如果存在多条同一天、同一线别、同一料号的数据(如下),则需要统计,否则不必。日期 线别 料号 数量
-----------------------------------
06/15/2004 06-01 A001 100.00
06/15/2004 06-01 A001 200.002、关于料号和成品号的对应关系。
victorycyz(中海)的方法在日期和线别后是按照插入语句的顺序来排序。
zjcxc(邹建)的方法在日期和线别后是按照料号/成品号名称来排序。日期 线别 料号 数量
-----------------------------------
06/15/2004 06-01 A001 100.00
06/15/2004 06-01 A002 100.00日期 线别 成品号 数量
-----------------------------------
06/15/2004 06-01 AAA 50.00
06/15/2004 06-01 BBB 50.00日期 线别 料号 数量 成品号 数量
-----------------------------------------------------------
06/15/2004 06-02 A001 100.00 AAA 50.00
06/15/2004 06-15 A002 100.00 BBB 50.00假设以上结果是正确的。那如果改为以下的数据了:日期 线别 料号 数量
-----------------------------------
06/15/2004 06-01 A002 100.00
06/15/2004 06-01 A001 100.00日期 线别 成品号 数量
-----------------------------------
06/15/2004 06-01 AAA 50.00
06/15/2004 06-01 BBB 50.00你要返回什么结果?日期 线别 料号 数量 成品号 数量
-----------------------------------------------------------
06/15/2004 06-02 A001 100.00 AAA 50.00
06/15/2004 06-15 A002 100.00 BBB 50.00===========================OR=================================日期 线别 料号 数量 成品号 数量
-----------------------------------------------------------
06/15/2004 06-02 A002 100.00 AAA 50.00
06/15/2004 06-15 A001 100.00 BBB 50.00victorycyz(中海)的方法返回的是后一种。如果再改为以下的数据了:日期 线别 料号 数量
-----------------------------------
06/15/2004 06-01 A001 100.00
06/15/2004 06-01 A002 100.00日期 线别 成品号 数量
-----------------------------------
06/15/2004 06-01 CCC 50.00
06/15/2004 06-01 BBB 50.00你要返回什么结果?日期 线别 料号 数量 成品号 数量
-----------------------------------------------------------
06/15/2004 06-02 A001 100.00 CCC 50.00
06/15/2004 06-15 A002 100.00 BBB 50.00===========================OR=================================日期 线别 料号 数量 成品号 数量
-----------------------------------------------------------
06/15/2004 06-02 A001 100.00 BBB 50.00
06/15/2004 06-15 A002 100.00 CCC 50.00zjcxc(邹建)的方法返回的是后一种。所以我觉得你缺少料号和成品号间对应的条件。
我先回答你的问题:
Q:
1、是否要进行统计,你现在用的victorycyz(中海)的方法是没有进行统计的。
如果存在多条同一天、同一线别、同一料号的数据(如下),则需要统计,否则不必。日期 线别 料号 数量
-----------------------------------
06/15/2004 06-01 A001 100.00
06/15/2004 06-01 A001 200.00A:
victorycyz(中海)的答案是少了统计这一步,我最终是要统计的,也就是说如果有上面两条记录则成了:
06/15/2004 06-01 A001 300.00
所以我说:victorycyz(中海)得到的明细数据是合乎要求的(还没汇总)
Q:
2、关于料号和成品号的对应关系。
victorycyz(中海)的方法在日期和线别后是按照插入语句的顺序来排序。
zjcxc(邹建)的方法在日期和线别后是按照料号/成品号名称来排序。
:::::A:事实上你所列举的所有答案对于我的需求来说都是可以的,实际我的需求只是以日期,线别,料号汇总投入部分(A),以日期,线别,成品号汇总产出部分(B),再把这两部分合并成一个表,同一天,同一线别的投入产出对应起来,那边多记录的对应另一边则为空。
所以,你列举的可以说是全是对的,只是排列顺序不同。
再看看我的举例的数据,它根据不同日期说明了不同情况
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
噢,Sorry,我的结果上的线别由于引用实际数据,但又没改全的原因,有错误!
正确为:
06/15/2004 06-01 A001 100.00 AAA 50.00
06/15/2004 06-01 A002 100.00 BBB 50.00
06/15/2004 06-01 A003 100.00
06/16/2004 06-02 AAA 50.00
06/16/2004 06-02 BBB 50.00
06/17/2004 06-03 A001 100.00 AAA 50.00--嘿嘿,难为他们可以看得明白~
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
接上
15号是:指投入汇总后的记录数比产出多,所以第三条的产出为NULL,而第一二条要和投入的一二条对应(位置)
16号是:完全没有投入,产出的记录加入记果集投入部分为NULL
17号是:汇总投入相同部分,得出的记录象上面两天的例子一样对应起来。
OK,大致说明了吧,而最后相信是zjcxc(邹建)的方法比较好的,他也帮我考虑了为置0,投入产出按日期,线别,名称排序。而之前用的方法跟victorycyz(中海)的类似(用临时表),victorycyz(中海)的要优化一点。还有最重要一个
为何料号和成品没有对应关系!?
PS:1。由于现时的数据是用于熔铸生产的,试想投入的是同是铁,产出是不同型号的成品,如何对应呢?
2。就算是电子生产相信每天投入的和每天产出数,也不是一一对应的。现时是客户要的所谓《每天投入产出〉是最后要每个线别的成品率的反正是比较特别吧!
-----------------------------------------------------------
06/15/2004 06-02 A001 100.00 AAA 50.00
06/15/2004 06-15 A002 100.00 BBB 50.00===========================OR=================================日期 线别 料号 数量 成品号 数量
-----------------------------------------------------------
06/15/2004 06-02 A002 100.00 AAA 50.00
06/15/2004 06-15 A001 100.00 BBB 50.00这可不是“排列顺序不同”的问题,上面的【A001】料号对应【AAA】成品号,下面的是【A002】料号对应【AAA】成品号。
比如:如果投料按【A001】、【A002】顺序,产出成品对应为【CCC】、【BBB】,
即-------------------------【A001-CCC,A002-BBB】。
如果登记数据时投料表按【A001】、【A002】顺序登记,
但产出表按【BBB】、【CCC】顺序登记,
按照victorycyz(中海)的方法查询的结果可能与你想要的不符,
查询的结果为---------------【A001-BBB,A002-CCC】。
当然,如果数据是电子设备自动获取、自动登记的就不存在这个问题。
但如果数据是自动登记,还是上面的数据,用zjcxc(邹建)的方法也与实际不符,
查询的结果为---------------【A001-BBB,A002-CCC】。
由于现时的数据是用于熔铸生产的,试想投入的是同是铁,产出是不同型号的成品,所以没有得对应的.To:zjcxc(邹建) 经转换,发现一个问题,右边的数据跟左边不能对上(变了全连接的交集了)
我在转换一下数据再测试一下,不行就把数据放上来