呼叫小三，修改SQL

wangxiaofeiwuqiao 2011-07-20 02:28:24

有表tb1，tb2联合查询组成如下视图（简称视图1,不必理会我的SQL语句）
视图1:



ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO      CTM_QTY

20-03304-14010     CSP3         500              0.15           75

20-94726-03010     CSP3         1600             0.65           1040

表tb1:(包含视图1所有的ITEM_CODE，主要为最后结果提供QTY_RATIO，不必理会其他数据)



ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO      CTM_QTY

20-03304-14010     CSP3         500              0.15           75

20-94726-03010     CSP3         1600             0.65           1040

21-03304-14010     CSP3         100              0.5            50

22-03304-14010     CSP3         200              0.9            180

21-94726-03010     CSP3         100              0.5            50

22-94726-03010     CSP3         200              0.8            160

90-94726-03010     CSP3         400              0.5            100

表：BOM1；其中PARENT与ITEM_CODE可以存在不确定的递归关系，比如最高层为99，然后是98,97,91,90,20,21,22等，20不是最高层的，21,22等最低层.



PARENT                  ITEM_CODE            QTY_PER(指ITEM_CODE的重量)   BOM_UNIT

97-03304-14010        91-03304-14010          1.5                          PCS

91-03304-14010        90-03304-14010          7.5                          PCS

90-03304-14010        20-03304-14010          0.5                          PCS

20-03304-14010        21-03304-14010          1.6                          G

20-03304-14010        22-03304-14020          0.8                          G

97-94726-03010        90-94726-03010          4.6                          PCS

90-94726-03010        20-94726-03010          8.8                          PCS

20-94726-03010        21-94726-03010          1.6                          G

20-94726-03010        22-94726-03010          9.8                          G

我想要的结果：：(来自视图1的ITEM_CODE，只要它的下面有下层，就要拆分到最低层)



PARENT             ITEM_CODE       LOCATION    P_QTY     STOCK_QTY     QTY_RATIO     CTM_QTY     BOM_UNIT

20-03304-14010   21-03304-14010      CSP1       500         800           0.5          400         G

20-03304-14010   22-03304-14010      CSP1       500         400           0.9          360         PCS

20-94726-03010   21-94726-03010      CSP1       1600        2560          0.5          1280        G

20-94726-03010   22-94726-03010      CSP1       1600        15680         0.8          12544       PCS 

其中P_QTY=视图1中的STOCK_QTY, STOCK_QTY=P_QTY*BOM1对应QTY_PER，QTY_RATIO来自tb1中对应的ITEM_CODE，CTM_QTY=STOCK_QTY*QTY_RATIO；

参考类似帖子：http://topic.csdn.net/u/20110719/17/e1f4cd3f-6156-4f38-aa04-0329899035c9.html?1014523738
数据库环境：sql 2000.最好一条sql搞定，因为我要将视图1的SQL插到最后的SQL中。

...全文

179 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

wangxiaofeiwuqiao 2011-07-20

打赏
举报

测试通过，OK。非常感谢小三。真厉害。

pt1314917 2011-07-20

打赏
举报

坐等小三。。。。

AcHerat 元老 2011-07-20

打赏
举报

mingpei0703 2011-07-20

打赏
举报

小三，看你的！

wangxiaofeiwuqiao 2011-07-20

打赏
举报

[Quote=引用 3 楼 acherat 的回复:]
这个。。。2000的话要拆分至最底层，肯定要用到函数：

SQL code

create table 视图1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,
QTY_RATIO float,CTM_QTY float)

insert into 视图1(ITEM_CODE,LOCATION,STOCK_QT……
[/Quote]
真快，我测试下先

wangxiaofeiwuqiao 2011-07-20

打赏
举报

tb1的测试数据可以省略为：



create table tb1(ITEM_CODE varchar(50),QTY_RATIO float)

set nocount on

insert into tb1(ITEM_CODE,QTY_RATIO,) 

values('20-03304-14010','0.15')

insert into tb1(ITEM_CODE,QTY_RATIO) 

values('20-94726-03010','0.65')

insert into tb1(ITEM_CODE,QTY_RATIO) 

values('21-03304-14010','0.5')

insert into tb1(ITEM_CODE,QTY_RATIO) 

values('22-03304-14010','0.9')

insert into tb1(ITEM_CODE,QTY_RATIO) 

values('21-94726-03010','0.5')

insert into tb1(ITEM_CODE,QTY_RATIO) 

values('22-94726-03010','0.8')

insert into tb1(ITEM_CODE,QTY_RATIO) 

values('90-94726-03010','1.2')

最后tb1表：



ITEM_CODE             QTY_RATIO      

20-03304-14010           0.15           

20-94726-03010           0.65           

21-03304-14010           0.5            

22-03304-14010           0.9            

21-94726-03010           0.5            

22-94726-03010           0.8            

90-94726-03010           0.5

cd731107 2011-07-20

打赏
举报

小三，快来，帮楼主顶一下

AcHerat 元老 2011-07-20

打赏
举报

这个。。。2000的话要拆分至最底层，肯定要用到函数：



create table 视图1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)



insert into 视图1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-03304-14010','CSP1','500','0.15','-7.5')

insert into 视图1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-94726-03010','CSP1','1600','0.65','1040')



create table tb1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)

set nocount on

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-03304-14010','CSP3','500','0.15','75')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-94726-03010','CSP3','1600','0.65','1040')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('21-03304-14010','CSP3','100','0.5','50')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('22-03304-14010','CSP3','200','0.9','180')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('21-94726-03010','CSP3','100','0.5','50')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('22-94726-03010','CSP3','200','0.8','160')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('90-94726-03010','CSP3','700','1.2','160')

go



Create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),

QTY_PER float,BOM_UNIT varchar(50))

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('97-03304-14010','91-03304-14010','1.5','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('91-03304-14010','90-03304-14010','7.5','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('90-03304-14010','20-03304-14010','0.5','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-03304-14010','21-03304-14010','1.6','G')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-03304-14010','22-03304-14010','0.8','G')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('97-94726-03010','90-94726-03010','4.6','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('90-94726-03010','20-94726-03010','8.8','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-94726-03010','21-94726-03010','1.6','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-94726-03010','22-94726-03010','9.8','G')

go



create function get_proC(@PARENT varchar(50))

returns varchar(1000)

as

begin

declare @t table(PARENT varchar(50),ITEM_CODE varchar(50),QTY_PER float,[level] int)

declare @i int

declare @ret varchar(1000)

declare @j int

set @j=1

set @i=1

insert into @t

select PARENT,ITEM_CODE,QTY_PER,@i from BOM1 where PARENT=@PARENT

while @@rowcount>0

begin

set @i=@i+1

insert into @t

select a.PARENT,a.ITEM_CODE,a.QTY_PER,@i from BOM1 a join @t b on a.PARENT=b.ITEM_CODE

where b.level=@i-1

end

select @j = max([level]) from @t

select @ret = isnull(@ret+',','')+ITEM_CODE+'='+ltrim(QTY_PER)

from @t

where [level] = @j

return @ret

end

go



select t.PARENT,t.ITEM_CODE,f.LOCATION,f.STOCK_QTY P_QTY,f.STOCK_QTY*t.QTY_PER STOCK_QTY,

    e.QTY_RATIO,f.STOCK_QTY*t.QTY_PER*e.QTY_RATIO CTM_QTY,t.BOM_UNIT

from(

    select a.PARENT,a.ITEM_CODE CODE,a.QTY_PER PER,

        isnull(cast(substring(','+dbo.get_proC(a.PARENT)+',',

        charindex(','+b.ITEM_CODE+'=',','+dbo.get_proC(a.PARENT)+',')+len(b.ITEM_CODE)+2,

        charindex(',',','+dbo.get_proC(a.PARENT)+',',

            charindex(','+b.ITEM_CODE+'=',','+dbo.get_proC(a.PARENT)+',')+1)

        -(charindex(','+b.ITEM_CODE+'=',','+dbo.get_proC(a.PARENT)+',')+len(b.ITEM_CODE)+2)) as float),a.QTY_PER) QTY_PER,

        a.BOM_UNIT,isnull(b.ITEM_CODE,a.ITEM_CODE) ITEM_CODE

    from BOM1 a left join 视图1 b

        on charindex(','+b.ITEM_CODE+'=',','+dbo.get_proC(a.PARENT)+',')>0

            and charindex(','+a.ITEM_CODE+'=',','+dbo.get_proC(a.PARENT)+',')=0

) t join tb1 e on t.ITEM_CODE = e.ITEM_CODE

    join 视图1 f on t.PARENT = f.ITEM_CODE





drop table 视图1,tb1,BOM1

drop function get_proC



/*********************



PARENT                                             ITEM_CODE                                          LOCATION                                           P_QTY                  STOCK_QTY              QTY_RATIO              CTM_QTY                BOM_UNIT

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

20-03304-14010                                     21-03304-14010                                     CSP1                                               500                    800                    0.5                    400                    G

20-03304-14010                                     22-03304-14010                                     CSP1                                               500                    400                    0.9                    360                    G

20-94726-03010                                     21-94726-03010                                     CSP1                                               1600                   2560                   0.5                    1280                   PCS

20-94726-03010                                     22-94726-03010                                     CSP1                                               1600                   15680                  0.8                    12544                  G

wangxiaofeiwuqiao 2011-07-20

打赏
举报

参考的帖子中与本贴不同的是：原来的视图1包含了所有的ITEM_CODE和QTY_RATIO，是错的。该贴才是正确的。是其中的一个表包含了所有的ITEM_CODE和QTY_RATIO。

wangxiaofeiwuqiao 2011-07-20

打赏
举报

测试数据：



create table 视图1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)



insert into 视图1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-03304-14010','CSP1','500','0.15','-7.5')

insert into 视图1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-94726-03010','CSP1','1600','0.65','1040')



create table tb1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,

QTY_RATIO float,CTM_QTY float)

set nocount on

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-03304-14010','CSP3','500','0.15','75')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('20-94726-03010','CSP3','1600','0.65','1040')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('21-03304-14010','CSP3','100','0.5','50')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('22-03304-14010','CSP3','200','0.9','180')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('21-94726-03010','CSP3','100','0.5','50')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('22-94726-03010','CSP3','200','0.8','160')

insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 

values('90-94726-03010','CSP3','700','1.2','160')

go



Create table BOM1(PARENT varchar(50),ITEM_CODE varchar(50),

QTY_PER float,BOM_UNIT varchar(50))

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('97-03304-14010','91-03304-14010','1.5','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('91-03304-14010','90-03304-14010','7.5','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('90-03304-14010','20-03304-14010','0.5','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-03304-14010','21-03304-14010','1.6','G')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-03304-14010','22-03304-14010','0.8','G')



insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('97-94726-03010','90-94726-03010','4.6','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('90-94726-03010','20-94726-03010','8.8','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-94726-03010','21-94726-03010','1.6','PCS')

insert into BOM1(PARENT,ITEM_CODE,QTY_PER,BOM_UNIT) 

values('20-94726-03010','22-94726-03010','9.8','G')