产品结构与提前期2

wolflove23 2010-09-07 08:41:28

前一个帖子已经结贴了，不过发现了个BUG 。希望还能给解决呵呵

if object_id('tb') is not null drop table tb

if object_id('tb2') is not null drop table tb2

CREATE TABLE [tb](    [PID]  varchar(100)  NULL ,    [CID]  varchar(100)  NULL ,    [NUM]  int NULL)

GO



--插入测试数据

INSERT INTO [tb] ([PID],[CID],[NUM])

    SELECT 'A','A1','1' UNION

    SELECT 'A','B','2' UNION

    SELECT 'A1','B1','1' UNION

    SELECT 'A1','B2','2'

GO



CREATE TABLE [tb2](    [Item]  varchar(100)  NULL ,    [GX]  VARCHAR(10)  NULL ,    [Leadtime]  int NULL)

GO 

--插入测试数据

INSERT INTO [tb2] ([Item],[GX],[Leadtime])

    SELECT 'A','100','3' UNION

    SELECT 'A','200','1' UNION

    SELECT 'B','100','1' UNION

    SELECT 'B','200','2' UNION

    SELECT 'A1','100','3' UNION

    SELECT 'A1','200','1' UNION

    SELECT 'B1','100','2' UNION

    SELECT 'B2','100','2'

GO

;

with cte as

(

select PID ,CID,(select sum(Leadtime) from tb2 where tb2.Item=tb.PID) LT  from tb  where PID='A'

union all

select a.PID,a.CID,LT+Leadtime

                   from tb a join cte b on a.[PID]=b.CID

                             join tb2 c on c.Item=a.PID                             

)

,

cte2 as 

(select a.*,Leadtime+isnull((select sum(Leadtime) from tb2 b where b.Item=a.Item and b.GX>a.GX),0)lt2 from tb2 a)





select a.Item,a.GX,dateadd(d,-(isnull(LT,0)+lt2),'2010-09-30') from cte2 a left join cte b on Item=CID

BOM 和工艺连接的时候因为连接字段都不是唯一产生错误啊

...全文

95 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

华夏小卒 2010-09-07

打赏
举报

with cte2 as
(
select PID ,CID, ( ---这个括号漏了

wolflove23 2010-09-07

打赏
举报

if object_id('tb') is not null drop table tb

if object_id('tb2') is not null drop table tb2

if object_id('#') is not null drop table #

CREATE TABLE [tb](    [PID]  varchar(100)  NULL ,    [CID]  varchar(100)  NULL ,    [NUM]  int NULL)

GO



--插入测试数据

INSERT INTO [tb] ([PID],[CID],[NUM])

    SELECT 'A','A1','1' UNION

    SELECT 'A','B','2' UNION

    SELECT 'A1','B1','1' UNION

    SELECT 'A1','B2','2'

GO



CREATE TABLE [tb2](    [Item]  varchar(100)  NULL ,    [GX]  VARCHAR(10)  NULL ,    [Leadtime]  int NULL)

GO 

--插入测试数据

INSERT INTO [tb2] ([Item],[GX],[Leadtime])

    SELECT 'A','100','3' UNION

    SELECT 'A','200','1' UNION

    SELECT 'B','100','1' UNION

    SELECT 'B','200','2' UNION

    SELECT 'A1','100','3' UNION

    SELECT 'A1','200','1' UNION

    SELECT 'B1','100','2' UNION

    SELECT 'B2','100','2'

GO

declare @A  int

SET @A=2



;

with cte as

(

select PID ,CID  from tb  where PID='A'

union all

select a.PID,a.CID

                   from tb a join cte b on a.[PID]=b.CID

                             

                            

)

select Item,sum(leadtime)as leadtime into # from tb2 where Item in(select distinct PID from cte) group by Item 

select * from #

这里运行正常，错误应该在CTE2附近

wolflove23 2010-09-07

打赏
举报

if object_id('tb') is not null drop table tb

if object_id('tb2') is not null drop table tb2

if object_id('#') is not null drop table #

CREATE TABLE [tb](    [PID]  varchar(100)  NULL ,    [CID]  varchar(100)  NULL ,    [NUM]  int NULL)

GO



--插入测试数据

INSERT INTO [tb] ([PID],[CID],[NUM])

    SELECT 'A','A1','1' UNION

    SELECT 'A','B','2' UNION

    SELECT 'A1','B1','1' UNION

    SELECT 'A1','B2','2'

GO



CREATE TABLE [tb2](    [Item]  varchar(100)  NULL ,    [GX]  VARCHAR(10)  NULL ,    [Leadtime]  int NULL)

GO 

--插入测试数据

INSERT INTO [tb2] ([Item],[GX],[Leadtime])

    SELECT 'A','100','3' UNION

    SELECT 'A','200','1' UNION

    SELECT 'B','100','1' UNION

    SELECT 'B','200','2' UNION

    SELECT 'A1','100','3' UNION

    SELECT 'A1','200','1' UNION

    SELECT 'B1','100','2' UNION

    SELECT 'B2','100','2'

GO

declare @A  int

SET @A=2



;

with cte as

(

select PID ,CID  from tb  where PID='A'

union all

select a.PID,a.CID

                   from tb a join cte b on a.[PID]=b.CID

                             

                            

)

select Item,sum(leadtime)as leadtime into # from tb2 where Item in(select distinct PID from cte) group by Item 

select * from # 



;

with cte2 as

(

select PID ,CID, select sum(Leadtime) from tb2 where tb2.Item=tb.PID) LT  from tb  where PID='A'

union all

select a.PID,a.CID,LT+leadtime

                   from tb a join cte2 b on a.[PID]=b.CID

                   join # c on c.Item=a.PID         

                            

)



select * from cte2

没运行看看错哪里了

华夏小卒 2010-09-07

打赏
举报

只有紧跟在with()后面的一句使用 cte 名，后面就没用了

如果想用：就再来一个

;with cte1 as
(...
)
,
cte2 as
(select * from cte1)

wolflove23 2010-09-07

打赏
举报

cte 这样的为什么只能用一次
select * from cte
select * from cte 这句话就不运行了：消息 208，级别 16，状态 1，第 17 行
对象名 'cte' 无效。

wolflove23 2010-09-07

打赏
举报

;
with cte as
(
select PID ,CID from tb where PID='A'
union all
select a.PID,a.CID
from tb a join cte b on a.[PID]=b.CID

)
select ITEM,sum(leadtime)as leadtime into # from tb2 where Item in(select distinct PID from cte) group by Item
select * from #
先用CTE 算一下汇总出所有父物料的工序提前期和，之后再用1楼的弄一边就能避免2表连接字段重复的问题，并且也省去了不能聚合的现象。

华夏小卒 2010-09-07

打赏
举报

[Quote=引用 7 楼 wolflove23 的回复:]
BOM 表里面还有一项台量没计算在内，实际上还要计算台量的1台A 或2台A 他的子物料要计算最终数量
[/Quote]可以在生成szy表，生成N列的时候，也生成一个乘积（台量1*提前期1*台量2*提前期2。。。）

wolflove23 2010-09-07

打赏
举报

[Quote=引用 11 楼 js_szy 的回复:]
看来我写的，不给分
[/Quote]

您误会了，怎么会不给呢？

华夏小卒 2010-09-07

打赏
举报

看来我写的，不给分

wolflove23 2010-09-07

打赏
举报

如果先计算 BOM 的话建一临时表存放不同的父物料，再连接工序表SUM，再更新BOM 表每个父物料的工序和。方法应该可以。

wolflove23 2010-09-07

打赏
举报

[Quote=引用 8 楼 duanzhi1984 的回复:]
又来了，呵，呵，不好意思啊
[/Quote]
来吧来吧，解决问题就给分 100不行就200 呵呵

duanzhi1984 2010-09-07

打赏
举报

又来了，呵，呵，不好意思啊

wolflove23 2010-09-07

打赏
举报

BOM 表里面还有一项台量没计算在内，实际上还要计算台量的1台A 或2台A 他的子物料要计算最终数量

wolflove23 2010-09-07

打赏
举报

好像大概可能明白了，主要是charindex一连串的函数弄晕了。SZY的作用以工序表为主，求出每个单件在BOM表里面的层次关系。最后 n=(select sum(Leadtime) from # where (charindex( item+'\', T.pt+'\')>0 AND item!=t.item ) OR (item=t.item and gx>=t.gx)) 这句话知道结果但是不明白为什么。

华夏小卒 2010-09-07

打赏
举报

 

--这样就比较容易看懂了

declare @A  DATETIME

SET @A='2010-9-30' 

 

;WITH SZY AS(

	 SELECT B.*,PT= cast(ISNULL(PID,'')+'\'+ITEM  as varchar(50))FROM TB2 B LEFT  JOIN Tb A  ON ITEM=A.CID

	 UNION ALL

	 SELECT S.Item,GX ,Leadtime,PT= cast(ISNULL(PID,'')+'\'+PT as varchar(50))   FROM SZY S ,Tb  A WHERE CHARINDEX(CID+'\', PT)>0 AND  LEFT(PT,CHARINDEX('\',PT)-1)!='A'

)

 select * into # from szy t where not exists(select 1 from szy  where iTEM=t.ITEM  and len( pt)>len(t.pt))

  



select Y.*,lt=dateadd(day,-n,@A)FROM (

	select t.*,

		   n=(select sum(Leadtime) from # where (charindex( item+'\',  T.pt+'\')>0 AND item!=t.item ) OR (item=t.item and gx>=t.gx)) 

	from # t

) Y

ORDER BY ITEM

--

Item                 GX         Leadtime    PT                                                 n           lt

-------------------- ---------- ----------- -------------------------------------------------- ----------- -----------------------

A                    100        3           \A                                                 4           2010-09-26 00:00:00.000

A                    200        1           \A                                                 1           2010-09-29 00:00:00.000

A1                   100        3           A\A1                                               8           2010-09-22 00:00:00.000

A1                   200        1           A\A1                                               5           2010-09-25 00:00:00.000

B                    100        1           A\B                                                7           2010-09-23 00:00:00.000

B                    200        2           A\B                                                6           2010-09-24 00:00:00.000

B1                   100        2           A\A1\B1                                            10          2010-09-20 00:00:00.000

B2                   100        2           A\A1\B2                                            10          2010-09-20 00:00:00.000



(8 行受影响)



drop table #

wolflove23 2010-09-07

打赏
举报

结果对。哎水平有限实在是看不懂啊

华夏小卒 2010-09-07

打赏
举报

if object_id('tb') is not null drop table tb

if object_id('tb2') is not null drop table tb2

CREATE TABLE [tb](    [PID]  varchar(100)  NULL ,    [CID]  varchar(100)  NULL ,    [NUM]  int NULL)

GO



--插入测试数据

INSERT INTO [tb] ([PID],[CID],[NUM])

    SELECT 'A','A1','1' UNION

    SELECT 'A','B','2' UNION

    SELECT 'A1','B1','1' UNION

    SELECT 'A1','B2','2'

GO



CREATE TABLE [tb2](    [Item]  varchar(20)  NULL ,    [GX]  VARCHAR(10)  NULL ,    [Leadtime]  int NULL)

GO 

--插入测试数据

INSERT INTO [tb2] ([Item],[GX],[Leadtime])

    SELECT 'A','100','3' UNION

    SELECT 'A','200','1' UNION

    SELECT 'B','100','1' UNION

    SELECT 'B','200','2' UNION

    SELECT 'A1','100','3' UNION

    SELECT 'A1','200','1' UNION

    SELECT 'B1','100','2' UNION

    SELECT 'B2','100','2'

GO



declare @A  DATETIME

SET @A='2010-9-30' 

 

;WITH SZY AS(

	 SELECT B.*,PT= cast(ISNULL(PID,'')+'\'+ITEM  as varchar(50))FROM TB2 B LEFT  JOIN Tb A  ON ITEM=A.CID

	 UNION ALL

	 SELECT S.Item,GX ,Leadtime,PT= cast(ISNULL(PID,'')+'\'+PT as varchar(50))   FROM SZY S ,Tb  A WHERE CHARINDEX(CID+'\', PT)>0 AND  LEFT(PT,CHARINDEX('\',PT)-1)!='A'

)

 select * into # from szy t where not exists(select 1 from szy  where iTEM=t.ITEM  and len( pt)>len(t.pt))

  

select t.item,t.gx,n=dateadd(day,-(select sum(Leadtime) from # where (charindex( item+'\',  T.pt+'\')>0 AND item!=t.item ) OR (item=t.item and gx>=t.gx)),@A)

from # t

ORDER BY ITEM

--



item                 gx         n

-------------------- ---------- -----------------------

A                    100        2010-09-26 00:00:00.000

A                    200        2010-09-29 00:00:00.000

A1                   100        2010-09-22 00:00:00.000

A1                   200        2010-09-25 00:00:00.000

B                    100        2010-09-23 00:00:00.000

B                    200        2010-09-24 00:00:00.000

B1                   100        2010-09-20 00:00:00.000

B2                   100        2010-09-20 00:00:00.000



(8 行受影响)

drop table #