How to generate the serial number of the query and update the table?

Category: MS-SQL Server -> Applications Author: liumengjiao0527 Date: 2001-07-08 20:48:25
 
liumengjiao0527
2001-07-08 20:48:25
/ *
Known test1 and test2
tes1:
material number of packaging layers date ; the starting serial number
001 ; first layer 2011-01-24 010
001 ; the second layer 2010-01-24 100

test2:
type wrapper layer material number Number Date Number of prints
001 The first layer is the first layer 2011-01-24 Label ; 3
001 mantissa label first layer of the first layer ; 2010-01-24 2
001 The second layer of the second layer label 2010-01-24 ; 2
001 mantissa label of the second layer Layer 2010-01-24 1

how to generate the following query?

type of packaging material number Date of printing layers
001 serial number ; first layer of the first layer label 2011-01-24 1 010
001 ; first layer label first layer 2011 was - 01-24 1 011
001 ; first layer of the first layer 2011-01-24 Label 1 012
001 ; first layer of the first layer 2011-01-24 mantissa tags ; 1 013
001 first layer of the first layer 2011-01-241 ending tag 014
001 second layer label ; the second layer 2011-01-241 100
001 second layer label ; the second layer 2011-01-241 101
001 second layer of the second layer ending tag ; 2011-01-241102 ;

then update the table test1 becomes:
test1:
material number of packaging layers ; dates starting serial number
001 The first layer 2011-01-24 015
The second layer 001 2010-01-24 ; 103

* /

- create a test environment

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] (
[item number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[packaging layers] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Date] [datetime] NULL,
[ starting serial number] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo]. [test2] (
[item number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Type] [varchar ] (50) COLLATE Chinese_PRC_CI_AS NULL,
[packaging layers] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL,
[date] [datetime] NULL,
[Print Quantity] [decimal] (18, 0) NULL
) ON [PRIMARY]
GO

Insert test1 (material number, packaging layers, date, start serial number) Values ​​('001 ',' first tier ', '2011- 01-24 ', '010')
Insert test1 (material number, packaging layers, date, start serial number) Values ​​('001 ',' second tier ' , '2011-01-24 ', '100')

Insert test2 (material number, type, packaging layers, date, number of prints) Values ​​(' 001, 'the first layer label', 'first layer', '2011-01-24 ', 3)
Insert test2 (material number, type, packaging layers, date, number of prints) ; Values ​​('001 ',' the first layer mantissa label ',' first tier ', '2011-01-24', 2)
Insert test2 (material number, type, packaging layers, date, number of prints) Values ​​('001 ',' second tier label ',' second tier ', '2011-01-24', 2)
Insert ; test2 (material number, type, packaging layers, date, number of prints) Values ​​('001 ',' second layer mantissa label ',' second tier ', '2011-01- 24 ', 1)
GO

select * from test1
select * from test2
tonight1991
2001-07-08 21:03:08

--
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.
zhaomangzheng
2001-07-08 21:41:40
partakers not answer a question?
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
jianpf
2001-07-08 22:03:25

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
*/


details yourself under the bar to adjust
bluesxiao
2001-07-08 22:29:31
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  )

*/
raybeam
2001-07-08 23:08:54
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  )

*/
DoraDYT
2001-07-08 23:37:36
# 1. prints not a record into a multi-line records:
achieve it with the cursor, do not know if there was a better way. (Your next post is how to achieve?)
# 2. Other words refer to your next post # 13 F