tb1ID 标识 数量 单号
1 A 1000 CN1
2 B 200 CN2
3 B 100 CN1
4 C 200 CN3
怎么根据TB1中的数据在TB2中插入数据TB2ID 序列号 单号
1 A00001 CN1
2 A00002 CN1
3 A00003 CN1
...
...
.
按TB1数量插入1000个,然后接着插入B
B00001 CN2
B00002 CN2
... 插入单号为CN2的数量200
..
B00200 CN2后面继续插入单号为CN1的数量100,但是由于标识相同,要求序列号要延续,B00201 CN1
B00202 CN1
....
....
B00300 CN1
C00001 CN3
.....
...序列号的生成规律要求能参照此帖http://bbs.csdn.net/topics/390536906
不知道我表述清楚了没,求高手回复,谢谢!
1 A 1000 CN1
2 B 200 CN2
3 B 100 CN1
4 C 200 CN3
怎么根据TB1中的数据在TB2中插入数据TB2ID 序列号 单号
1 A00001 CN1
2 A00002 CN1
3 A00003 CN1
...
...
.
按TB1数量插入1000个,然后接着插入B
B00001 CN2
B00002 CN2
... 插入单号为CN2的数量200
..
B00200 CN2后面继续插入单号为CN1的数量100,但是由于标识相同,要求序列号要延续,B00201 CN1
B00202 CN1
....
....
B00300 CN1
C00001 CN3
.....
...序列号的生成规律要求能参照此帖http://bbs.csdn.net/topics/390536906
不知道我表述清楚了没,求高手回复,谢谢!
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [ID] INT, [标识] varchar(100), [数量] INT, [单号] varchar(100));
insert #temp
select '1','A','1000','CN1' union all
select '2','B','200','CN2' union all
select '3','B','100','CN1' union all
select '4','C','200','CN3' CREATE TABLE #tempB(id INT, 序列号 VARCHAR(100), 单号 VARCHAR(100))
--SQL:
;WITH
cte1 AS(SELECT num = 1 UNION ALL SELECT 1),
cte2 AS(SELECT num = 1 FROM cte1 a, cte1 b),
cte3 AS(SELECT num = 1 FROM cte2 a, cte2 b),
cte4 AS(SELECT num = 1 FROM cte3 a, cte3 b),
cte5 AS(SELECT num = 1 FROM cte4 a, cte4 b),
cteNum as(SELECT num = ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM cte5),
Data AS
(
select *,beginno=ISNULL((SELECT SUM([数量]) FROM #temp b WHERE b.[标识]=A.[标识] AND B.ID < A.ID),0)+1
from #temp A
)
INSERT INTO #tempB(id, 序列号, 单号)
SELECT A.id, a.[标识]+RIGHT('00000'+RTRIM(B.num), 5), A.[单号]
FROM Data A
INNER JOIN cteNum B
ON B.num BETWEEN A.beginno AND (a.beginno+a.数量-1)SELECT * FROM #tempB
--结果自己看:
create table tb1
(ID int, 标识 varchar(5), 数量 int, 单号 varchar(5))insert into tb1
select 1, 'A', 30, 'CN1' union all --> 为显示方便,测试数据减少数量级.
select 2, 'B', 20, 'CN2' union all
select 3, 'B', 10, 'CN1' union all
select 4, 'C', 20, 'CN3'create table tb2
(ID int, 序列号 varchar(10), 单号 varchar(5))
-- 插入tb2
insert into tb2(ID,序列号,单号)
select row_number() over(order by getdate()),
c.标识+replicate('0',5-len(d.n))+rtrim(d.n),
c.单号
from (select a.标识,a.数量,a.单号,
(select sum(b.数量) from tb1 b
where b.标识=a.标识 and b.ID<a.ID) 'q'
from tb1 a) c
cross apply(select isnull(c.q,0)+number 'n' from master.dbo.spt_values
where type='P' and number>=1 and number<=c.数量) d
-- 结果
select ID,序列号,单号 from tb2
/*
ID 序列号 单号
----------- ---------- -----
1 A00001 CN1
2 A00002 CN1
3 A00003 CN1
4 A00004 CN1
5 A00005 CN1
6 A00006 CN1
7 A00007 CN1
8 A00008 CN1
9 A00009 CN1
10 A00010 CN1
11 A00011 CN1
12 A00012 CN1
13 A00013 CN1
14 A00014 CN1
15 A00015 CN1
16 A00016 CN1
17 A00017 CN1
18 A00018 CN1
19 A00019 CN1
20 A00020 CN1
21 A00021 CN1
22 A00022 CN1
23 A00023 CN1
24 A00024 CN1
25 A00025 CN1
26 A00026 CN1
27 A00027 CN1
28 A00028 CN1
29 A00029 CN1
30 A00030 CN1
31 B00001 CN2
32 B00002 CN2
33 B00003 CN2
34 B00004 CN2
35 B00005 CN2
36 B00006 CN2
37 B00007 CN2
38 B00008 CN2
39 B00009 CN2
40 B00010 CN2
41 B00011 CN2
42 B00012 CN2
43 B00013 CN2
44 B00014 CN2
45 B00015 CN2
46 B00016 CN2
47 B00017 CN2
48 B00018 CN2
49 B00019 CN2
50 B00020 CN2
51 B00021 CN1
52 B00022 CN1
53 B00023 CN1
54 B00024 CN1
55 B00025 CN1
56 B00026 CN1
57 B00027 CN1
58 B00028 CN1
59 B00029 CN1
60 B00030 CN1
61 C00001 CN3
62 C00002 CN3
63 C00003 CN3
64 C00004 CN3
65 C00005 CN3
66 C00006 CN3
67 C00007 CN3
68 C00008 CN3
69 C00009 CN3
70 C00010 CN3
71 C00011 CN3
72 C00012 CN3
73 C00013 CN3
74 C00014 CN3
75 C00015 CN3
76 C00016 CN3
77 C00017 CN3
78 C00018 CN3
79 C00019 CN3
80 C00020 CN3(80 row(s) affected)
*/
比如有2W希望结果是:A标识1W5/CN号为CN1,B标识200的CN2,100的CN1
A A0000 CN1
...
A A9999 CN1
后面5位是序列号
A B0001 CN1
...
A B4999 CN1
B A0000 CN2
B A0001 CN2
B A0200 CN2
B A0201 CN1
....
B A0300 CN1
ID 标识 数量 单号
----------- ----- ----------- -----
1 A 30 CN1
2 B 20 CN2
3 B 10 CN1
4 C 20 CN3
B0000 B0002 ~~B9999
C0000 C0002 ~~C9999
ID 序列号 单号
----------- ---------- -----
1 AA0001 CN1
2 AA0002 CN1
3 AA0003 CN1
4 AA0004 CN1
5 AA0005 CN1
6 AA0006 CN1
7 AA0007 CN1
.....9 AA9999 CN1
10 AB0000 CN1
11 AB0001 CN1
12 AB0002 CN1
13 AB0003 CN1
14 AB0004 CN1
15 AB0005 CN1
16 AB0006 CN1
17 AB0007 CN1
18 AB0008 CN1
19 AB0009 CN1
20 AB0010 CN1
21 AB0011 CN1
22 AB0012 CN1
23 AB0013 CN1
24 AB0014 CN1
25 AB0015 CN1
26 AB0016 CN1
27 AB0017 CN1
28 AB0018 CN1
29 AB0019 CN1
30 AB0020 CN131 BA0001 CN2
32 BA0002 CN2
33 BA0003 CN2
34 BA0004 CN2
35 BA0005 CN2
36 BA0006 CN2
37 BA0007 CN2
38 BA0008 CN2
39 BA0009 CN2
40 BA0010 CN2
41 BA0011 CN2
42 BA0012 CN2
43 BA0013 CN2
44 BA0014 CN2
45 BA0015 CN2
46 BA0016 CN2
47 BA0017 CN2
48 BA0018 CN2
49 BA0019 CN2
50 BA0020 CN2
51 BA0021 CN1
52 BA0022 CN1
53 BA0023 CN1
54 BA0024 CN1
55 BA0025 CN1
56 BA0026 CN1
57 BA0027 CN1
58 BA0028 CN1
59 BA0029 CN1
60 BA0030 CN1
61 CA0001 CN3
62 CA0002 CN3
63 CA0003 CN3
64 CA0004 CN3
65 CA0005 CN3
66 CA0006 CN3
67 CA0007 CN3
68 CA0008 CN3
69 CA0009 CN3
70 CA0010 CN3
71 CA0011 CN3
72 CA0012 CN3
73 CA0013 CN3
74 CA0014 CN3
75 CA0015 CN3
76 CA0016 CN3
77 CA0017 CN3
78 CA0018 CN3
79 CA0019 CN3
80 CA0020 CN3
(80 row(s) affected)
*/
1 D 10020 CN1
2 H 20 CN2
3 H 10 CN1
4 K 20 CN3TB2
/*
ID 序列号 单号
----------- ---------- -----
1 DA0001 CN1
2 DA0002 CN1
3 DA0003 CN1
4 DA0004 CN1
5 DA0005 CN1
6 DA0006 CN1
7 DA0007 CN1
.....9 DA9999 CN1
10 DB0000 CN1
11 DB0001 CN1
12 DB0002 CN1
13 DB0003 CN1
14 DB0004 CN1
15 DB0005 CN1
16 DB0006 CN1
17 DB0007 CN1
18 DB0008 CN1
19 DB0009 CN1
20 DB0010 CN1
21 DB0011 CN1
22 DB0012 CN1
23 DB0013 CN1
24 DB0014 CN1
25 DB0015 CN1
26 DB0016 CN1
27 DB0017 CN1
28 DB0018 CN1
29 DB0019 CN1
30 DB0020 CN131 HA0001 CN2
32 HA0002 CN2
33 HA0003 CN2
34 HA0004 CN2
35 HA0005 CN2
36 HA0006 CN2
37 HA0007 CN2
38 HA0008 CN2
39 HA0009 CN2
40 HA0010 CN2
41 HA0011 CN2
42 HA0012 CN2
43 HA0013 CN2
44 HA0014 CN2
45 HA0015 CN2
46 HA0016 CN2
47 HA0017 CN2
48 HA0018 CN2
49 HA0019 CN2
50 HA0020 CN2
51 HA0021 CN1
52 HA0022 CN1
53 HA0023 CN1
54 HA0024 CN1
55 HA0025 CN1
56 HA0026 CN1
57 HA0027 CN1
58 HA0028 CN1
59 HA0029 CN1
60 HA0030 CN1
61 KA0001 CN3
62 KA0002 CN3
63 KA0003 CN3
64 KA0004 CN3
65 KA0005 CN3
66 KA0006 CN3
67 KA0007 CN3
68 KA0008 CN3
69 KA0009 CN3
70 KA0010 CN3
71 KA0011 CN3
72 KA0012 CN3
73 KA0013 CN3
74 KA0014 CN3
75 KA0015 CN3
76 KA0016 CN3
77 KA0017 CN3
78 KA0018 CN3
79 KA0019 CN3
80 KA0020 CN3
*/
if OBJECT_ID('tempdb..#temp1', 'u') is not null drop table #temp1;
create table #temp1( [ID] INT, [标识] varchar(100), [数量] INT, [单号] varchar(100));
insert #temp1
select '1','A','1000','CN1' union all
select '2','B','200','CN2' union all
select '3','B','100','CN1' union all
select '4','C','200','CN3' if OBJECT_ID('tempdb..#temp2', 'u') is not null drop table #temp2;
CREATE TABLE #temp2(id INT, [序列号] VARCHAR(100), [单号] VARCHAR(100));
WITH a1 AS
(
SELECT *,ISNULL((SELECT SUM([数量]) FROM #temp1 WHERE [标识]=a.[标识] AND id<a.id),0) lj_qty
FROM #temp1 a
)
,a2 AS
(
SELECT [ID],[标识],[数量],[单号],1 n,lj_qty+1 n2 FROM a1
UNION ALL
SELECT [ID],[标识],[数量],[单号],1+n,1+n2 FROM a2
WHERE n<[数量]
)
INSERT #temp2
SELECT ROW_NUMBER() OVER (ORDER BY id,n2) id,
[标识]+CHAR((n2-1)/10000+65)+RIGHT('000'+CONVERT(VARCHAR(4),n2-(n2-1)/10000*10000-1),4) [序列号],
[单号]
FROM a2
OPTION(MAXRECURSION 999)SELECT * FROM #temp2
INSERT #temp2 SELECT ROW_NUMBER() OVER (ORDER BY id,n2) id, [序列号], [单号] FROM a2 @lzw_0736 这句不插入id怎么弄?去掉id列就出错