如何生成这个流水号的查询和更新表?

SKY1006 2011-01-24 03:39:56

/*
已知test1与test2
tes1：
物料编号包装层数日期起始流水号
001 第一层 2011-01-24 010
001 第二层 2010-01-24 100

test2:
物料编号类型包装层数日期打印数量
001 第一层标签第一层 2011-01-24 3
001 第一层尾数标签第一层 2010-01-24 2
001 第二层标签第二层 2010-01-24 2
001 第二层尾数标签第二层 2010-01-24 1

如何生成如下查询?

物料编号类型包装层数日期打印数量流水号
001 第一层标签第一层 2011-01-24 1 010
001 第一层标签第一层 2011-01-24 1 011
001 第一层标签第一层 2011-01-24 1 012
001 第一层尾数标签第一层 2011-01-24 1 013
001 第一层尾数标签第一层 2011-01-24 1 014
001 第二层标签第二层 2011-01-24 1 100
001 第二层标签第二层 2011-01-24 1 101
001 第二层尾数标签第二层 2011-01-24 1 102

然后更新表test1变成：
test1：
物料编号包装层数日期起始流水号
001 第一层 2011-01-24 015
001 第二层 2010-01-24 103

*/

--建立测试环境

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test2]
GO

CREATE TABLE [dbo].[test1] (
[物料编号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[包装层数] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[日期] [datetime] NULL ,
[起始流水号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[test2] (
[物料编号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[类型] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[包装层数] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[日期] [datetime] NULL ,
[打印数量] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

Insert test1 (物料编号,包装层数,日期,起始流水号) Values ( '001','第一层','2011-01-24','010')
Insert test1 (物料编号,包装层数,日期,起始流水号) Values ( '001','第二层','2011-01-24','100')

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第一层标签','第一层','2011-01-24',3)
Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第一层尾数标签','第一层','2011-01-24',2)
Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第二层标签','第二层','2011-01-24',2)
Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第二层尾数标签','第二层','2011-01-24',1)
GO

select * from test1
select * from test2

...全文

313 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

Shawn 2011-01-24

打赏
举报

#1.打印数量不为1的记录变成多行记录:
用游标实现吧，还不知道有没有更好的办法。（你的下一贴是怎样实现的？）
#2.其它的话参考你的下一贴#13楼
http://topic.csdn.net/u/20110124/18/f9abba82-bd70-4e9c-918c-ae04a5f27111.html?93387

叶子 2011-01-24

打赏
举报



--查询

SELECT  a.物料编号 ,

        a.类型 ,

        a.包装层数 ,

        CONVERT(VARCHAR(10), a.日期, 120) AS 日期 ,

        a.打印数量 ,

        b.起始流水号 ,

        RIGHT('000' + CAST(CAST(( SELECT    COUNT(物料编号) AS [COUNT]

                                  FROM      test3

                                  WHERE     物料编号 = a.物料编号

                                            AND 包装层数 = a.包装层数

                                            AND 自动编号 < a.自动编号

                                ) + b.起始流水号 AS INT) AS VARCHAR(6)), 3) AS 流水号

FROM    test3 a

        LEFT JOIN dbo.test1 b ON a.物料编号 = b.物料编号

                                 AND a.包装层数 = b.包装层数

--更新                                

UPDATE test1

SET  起始流水号=RIGHT('000'+CAST(cc.新值 AS VARCHAR(10)),3)

FROM test1 aa

LEFT JOIN (

SELECT 物料编号,包装层数,MAX(CAST(流水号 AS int)+1) AS 新值 FROM (

SELECT  a.物料编号 ,

        a.类型 ,

        a.包装层数 ,

        CONVERT(VARCHAR(10), a.日期, 120) AS 日期 ,

        a.打印数量 ,

        b.起始流水号 ,

        RIGHT('000' + CAST(CAST(( SELECT    COUNT(物料编号) AS [COUNT]

                                  FROM      test3

                                  WHERE     物料编号 = a.物料编号

                                            AND 包装层数 = a.包装层数

                                            AND 自动编号 < a.自动编号

                                ) + b.起始流水号 AS INT) AS VARCHAR(6)), 3) AS 流水号

FROM    test3 a

        LEFT JOIN dbo.test1 b ON a.物料编号 = b.物料编号

                                 AND a.包装层数 = b.包装层数

                                 )bb GROUP BY 物料编号,包装层数

) cc ON aa.物料编号=cc.物料编号 AND aa.包装层数=cc.包装层数

cnkiminzhuhu 2011-01-24

打赏
举报

业务看不懂

-晴天 2011-01-24

打赏
举报

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[test1]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[test2]

GO



CREATE TABLE [dbo].[test1] (

[物料编号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[包装层数] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[日期] [datetime] NULL ,

[起始流水号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL  

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[test2] (

[物料编号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[类型] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[包装层数] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,

[日期] [datetime] NULL ,

[打印数量] [decimal](18, 0) NULL  

) ON [PRIMARY]

GO



Insert test1 (物料编号,包装层数,日期,起始流水号) Values ( '001','第一层','2011-01-24','010')

Insert test1 (物料编号,包装层数,日期,起始流水号) Values ( '001','第二层','2011-01-24','100')



Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第一层标签','第一层','2011-01-24',3)

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第一层尾数标签','第一层','2011-01-24',2)

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第二层标签','第二层','2011-01-24',2)

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第二层尾数标签','第二层','2011-01-24',1)

GO



;with cte as(

select a.物料编号,a.类型,a.包装层数,a.日期,1 as 打印数量,convert(varchar(3),b.起始流水号) as 流水号,convert(int,a.打印数量) as flg

from test2 a inner join test1 b on a.物料编号=b.物料编号 and a.包装层数=b.包装层数 where charindex('尾数',a.类型)=0

union all

select a.物料编号,b.类型,a.包装层数,a.日期,1 as 打印数量,convert(varchar(3),right('000'+ltrim(a.起始流水号+c.打印数量),3)) as 流水号,convert(int,b.打印数量) as flg

from test1 a inner join test2 b on a.物料编号=b.物料编号 and a.包装层数=b.包装层数

inner join test2 c on a.物料编号=c.物料编号 and a.包装层数=c.包装层数

where charindex('尾数',b.类型)>0 and charindex('尾数',c.类型)=0

union all

select 物料编号,类型,包装层数,日期,打印数量,convert(varchar(3),right('000'+ltrim(流水号+1),3))as 流水号,flg-1 as flg from cte where flg>1

)select 物料编号,包装层数,right('000'+ltrim(max(流水号)+1),3)as 流水号 into # FROM cte group by 物料编号,包装层数

update test1 set 起始流水号=b.流水号 from test1 a inner join # b on a.物料编号=b.物料编号 and a.包装层数=b.包装层数

select * from test1

go

drop table #

/*

物料编号                                               包装层数                                               日期                      起始流水号

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

001                                                第一层                                                2011-01-24 00:00:00.000 015

001                                                第二层                                                2011-01-24 00:00:00.000 103



(2 行受影响)



*/

-晴天 2011-01-24

打赏
举报

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[test1]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[test2]

GO



CREATE TABLE [dbo].[test1] (

[物料编号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[包装层数] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[日期] [datetime] NULL ,

[起始流水号] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL  

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[test2] (

[物料编号] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[类型] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[包装层数] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,

[日期] [datetime] NULL ,

[打印数量] [decimal](18, 0) NULL  

) ON [PRIMARY]

GO



Insert test1 (物料编号,包装层数,日期,起始流水号) Values ( '001','第一层','2011-01-24','010')

Insert test1 (物料编号,包装层数,日期,起始流水号) Values ( '001','第二层','2011-01-24','100')



Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第一层标签','第一层','2011-01-24',3)

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第一层尾数标签','第一层','2011-01-24',2)

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第二层标签','第二层','2011-01-24',2)

Insert test2 (物料编号,类型,包装层数,日期,打印数量) Values ( '001','第二层尾数标签','第二层','2011-01-24',1)

GO

;with cte as(

select a.物料编号,a.类型,a.包装层数,a.日期,1 as 打印数量,convert(varchar(3),b.起始流水号) as 流水号,convert(int,a.打印数量) as flg

from test2 a inner join test1 b on a.物料编号=b.物料编号 and a.包装层数=b.包装层数 where charindex('尾数',a.类型)=0

union all

select a.物料编号,b.类型,a.包装层数,a.日期,1 as 打印数量,convert(varchar(3),right('000'+ltrim(a.起始流水号+c.打印数量),3)) as 流水号,convert(int,b.打印数量) as flg

from test1 a inner join test2 b on a.物料编号=b.物料编号 and a.包装层数=b.包装层数

inner join test2 c on a.物料编号=c.物料编号 and a.包装层数=c.包装层数

where charindex('尾数',b.类型)>0 and charindex('尾数',c.类型)=0

union all

select 物料编号,类型,包装层数,日期,打印数量,convert(varchar(3),right('000'+ltrim(流水号+1),3))as 流水号,flg-1 as flg from cte where flg>1

)select 物料编号,类型,包装层数,日期,打印数量,流水号 FROM cte order by 流水号

/*

物料编号                                               类型                                                 包装层数                                               日期                      打印数量        流水号

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

001                                                第一层标签                                              第一层                                                2011-01-24 00:00:00.000 1           010

001                                                第一层标签                                              第一层                                                2011-01-24 00:00:00.000 1           011

001                                                第一层标签                                              第一层                                                2011-01-24 00:00:00.000 1           012

001                                                第一层尾数标签                                            第一层                                                2011-01-24 00:00:00.000 1           013

001                                                第一层尾数标签                                            第一层                                                2011-01-24 00:00:00.000 1           014

001                                                第二层标签                                              第二层                                                2011-01-24 00:00:00.000 1           100

001                                                第二层标签                                              第二层                                                2011-01-24 00:00:00.000 1           101

001                                                第二层尾数标签                                            第二层                                                2011-01-24 00:00:00.000 1           102



(8 行受影响)



*/

叶子 2011-01-24

打赏
举报



declare @tes1 table (物料编号 varchar(3),包装层数 varchar(6),日期 datetime,起始流水号 varchar(3))

insert into @tes1

select '001','第一层','2011-01-24','010' union all

select '001','第二层','2010-01-24','100'



declare @test2 table (物料编号 varchar(3),类型 varchar(14),包装层数 varchar(6),日期 datetime,打印数量 int)

insert into @test2

select '001','第一层标签','第一层','2011-01-24',3 union all

select '001','第一层尾数标签','第一层','2010-01-24',2 union all

select '001','第二层标签','第二层','2010-01-24',2 union all

select '001','第二层尾数标签','第二层','2010-01-24',1



SELECT aa.物料编号,aa.类型,aa.包装层数,CONVERT(VARCHAR(10),aa.日期,120) AS 日期

,1 AS 打印数量,ROW_NUMBER()

OVER (PARTITION BY aa.包装层数 ORDER BY aa.物料编号)-1+CAST(bb.起始流水号 AS INT) AS 起始流水号 FROM(

SELECT * FROM @test2 WHERE 打印数量=3 UNION all

SELECT * FROM @test2 WHERE 打印数量=3 UNION all

SELECT * FROM @test2 WHERE 打印数量=3 UNION all

SELECT * FROM @test2 WHERE 打印数量=2 UNION ALL

SELECT * FROM @test2 WHERE 打印数量=2 UNION ALL

SELECT * FROM @test2 WHERE 打印数量=1)

aa LEFT JOIN @tes1 bb ON aa.包装层数=bb.包装层数



/*

物料编号 类型             包装层数   日期         打印数量        起始流水号

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

001  第二层标签          第二层    2010-01-24 1           100

001  第二层标签          第二层    2010-01-24 1           101

001  第二层尾数标签        第二层    2010-01-24 1           102

001  第一层尾数标签        第一层    2010-01-24 1           10

001  第一层标签          第一层    2011-01-24 1           11

001  第一层标签          第一层    2011-01-24 1           12

001  第一层标签          第一层    2011-01-24 1           13

001  第一层尾数标签        第一层    2010-01-24 1           14

*/

细节上你自己调整下吧

快溜 2011-01-24

打赏
举报

答一题有分不？

update a set 

起始流水号=stuff('000',4-len(cast(起始流水号 as int)+(select sum(打印数量) from test2 where 物料编号=a.物料编号 and 包装层数=a.包装层数)),

                        len(cast(起始流水号 as int)+(select sum(打印数量) from test2 where 物料编号=a.物料编号 and 包装层数=a.包装层数)),

                         cast(起始流水号 as int)+(select sum(打印数量) from test2 where 物料编号=a.物料编号 and 包装层数=a.包装层数))

 from test1 a