现有一个表,表名:tmxxb 如下
控制号 条形码 登录号 系统号
1 20000001 20000001 1
1 20000002 20000001 2
2 20000003 20000002 3
3 20000004 20000004 4现在想在每个不一样的控制号下加5条数据,条形码和登录号不能重复
请问怎么办呀??
控制号 条形码 登录号 系统号
1 20000001 20000001 1
1 20000002 20000001 2
2 20000003 20000002 3
3 20000004 20000004 4现在想在每个不一样的控制号下加5条数据,条形码和登录号不能重复
请问怎么办呀??
INSERT @TB
SELECT 1, 20000001, 20000001, 1 UNION ALL
SELECT 1, 20000002, 20000001, 2 UNION ALL
SELECT 2, 20000003, 20000002, 3 UNION ALL
SELECT 3, 20000004, 20000004, 4INSERT @TB
SELECT 控制号,条形码+ID AS 条形码,登录号+ID AS 登录号,系统号
FROM (SELECT 控制号,MAX(条形码) AS 条形码,MAX(登录号) AS 登录号,MAX(系统号) AS 系统号 FROM @TB GROUP BY 控制号 ) A,
(SELECT ID=1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5) BSELECT * FROM @TB ORDER BY 控制号,条形码,登录号/*
控制号 条形码 登录号 系统号
----------- -------- -------- -----------
1 20000001 20000001 1
1 20000002 20000001 2
1 20000003 20000002 2
1 20000004 20000003 2
1 20000005 20000004 2
1 20000006 20000005 2
1 20000007 20000006 2
2 20000003 20000002 3
2 20000004 20000003 3
2 20000005 20000004 3
2 20000006 20000005 3
2 20000007 20000006 3
2 20000008 20000007 3
3 20000004 20000004 4
3 20000005 20000005 4
3 20000006 20000006 4
3 20000007 20000007 4
3 20000008 20000008 4
3 20000009 20000009 4(19 row(s) affected)
*/
DECLARE @TB TABLE([控制号] INT, [条形码] VARCHAR(8), [登录号] VARCHAR(8), [系统号] INT)
INSERT @TB
SELECT 1, 20000001, 20000001, 1 UNION ALL
SELECT 1, 20000002, 20000001, 2 UNION ALL
SELECT 2, 20000003, 20000002, 3 UNION ALL
SELECT 3, 20000004, 20000004, 4
Create table #tmxxb (a int,b int ,c int ,d int)
insert into #tmxxb
select 1 , 20000001 , 20000001 , 1 union all
select 1 , 20000002 , 20000001 , 2 union all
select 2 , 20000003 , 20000002 , 3 union all
select 3 , 20000004 , 20000004 , 4
delete #tmxxbinsert into #tmxxb
select a , row_number() over (order by a)+(select max(b) from #tmxxb)
, row_number() over (order by a)+(select max(c) from #tmxxb)
, row_number() over (order by a)+(select max(d) from #tmxxb) from
(select a,max(b) b,max(c) c,Max(d) d from #tmxxb Group by a) tb,(
select 1 as e union all
select 2 as e union all
select 3 as e union all
select 4 as e union all
select 5 as e ) tb2select * from #tmxxb----------结果-----------
1 20000002 20000001 2
2 20000003 20000002 3
3 20000004 20000004 4
1 20000005 20000005 5
1 20000006 20000006 6
1 20000007 20000007 7
1 20000008 20000008 8
1 20000009 20000009 9
2 20000010 20000010 10
2 20000011 20000011 11
2 20000012 20000012 12
2 20000013 20000013 13
2 20000014 20000014 14
3 20000015 20000015 15
3 20000016 20000016 16
3 20000017 20000017 17
3 20000018 20000018 18
3 20000019 20000019 19
insert into #tmxxb
select a , row_number() over (order by a)+(select max(b) from #tmxxb)
, row_number() over (order by a)+(select max(c) from #tmxxb)
, row_number() over (order by a)+(select max(d) from #tmxxb) from
(select a,max(b) b,max(c) c,Max(d) d from #tmxxb Group by a) tb,(
select 1 as e union all
select 2 as e union all
select 3 as e union all
select 4 as e union all
select 5 as e ) tb2
登录号如果可以重复的话,把这个改为:
row_number() over (order by a)+(select max(d) from #tmxxb)
改为
d 就ok了!
select a
, row_number() over (order by a) --返回结果集分区内行的序列号
+(select max(b) from #tmxxb) --取表#tmxxb中最大的b
--用行号+最大的ID号 = 一个新的数字,因为这里max(b)他永远取的都是20000004这条记录,所以我们要产生一条
--不重复的数字,我们得用行号加上max(b)才是不会重复的数字。
, row_number() over (order by a)
+(select max(c) from #tmxxb)
,d from
(select a,max(d) d from #tmxxb Group by a) tb,
--上面是根据#tmxxb a 列进行分组,又因为楼主要求 A列如果相同,则取一条记录。
(select 1 as e union all
select 2 as e union all
select 3 as e union all
select 4 as e union all
select 5 as e ) tb2
--此部分不需要解释了吧,应楼主要求,每个不一样的A列号下加5条数据,条形码和登录号不能重复
--其实就是 过滤 tb表的数据,为3条,tb表的数据为5条,则最后2个表一起查出来就是15条记录了。
Create table ts
(控制号 int,条形码 int ,登录号 int ,系统号 int)
insert into ts
select 1,20000001,20000001,1
union all
select 1,20000002,20000001,2
union all
select 2,20000003,20000002,3
union all
select 3,20000004,20000004,4 declare @a int,@b int,@c int,@d int
set @a=4
while @a<=8
begin
insert into ts
select 1,20000002+@a,20000002+@a,1
set @a=@a+1
end
set @b=9
while @b<=13
begin
insert into ts
select 1,20000002+@b,20000002+@b,2
set @b=@b+1
end
set @c=12
while @c<=16
begin
insert into ts
select 2,20000003+@c,20000002+@c,3
set @c=@c+1
end
set @d=16
while @d<=20
begin
insert into ts
select 3,20000004+@d,20000004+@d,4
set @d=@d+1
end
select * from ts
drop table ts简单的循环游标实现。
----------------------------
控制号 条形码 登录号 系统号
1 20000001 20000001 1
1 20000002 20000001 2
2 20000003 20000002 3
3 20000004 20000004 4
1 20000006 20000006 1
1 20000007 20000007 1
1 20000008 20000008 1
1 20000009 20000009 1
1 20000010 20000010 1
1 20000011 20000011 2
1 20000012 20000012 2
1 20000013 20000013 2
1 20000014 20000014 2
1 20000015 20000015 2
2 20000015 20000014 3
2 20000016 20000015 3
2 20000017 20000016 3
2 20000018 20000017 3
2 20000019 20000018 3
3 20000020 20000020 4
3 20000021 20000021 4
3 20000022 20000022 4
3 20000023 20000023 4
3 20000024 20000024 4
Create table ts
(控制号 int,条形码 int ,登录号 int ,系统号 int)
insert into ts
select 1,20000001,20000001,1
union all
select 1,20000002,20000001,2
union all
select 2,20000003,20000002,3
union all
select 3,20000004,20000004,4 declare @a int,@b int,@c int,@d int
set @a=3
while @a<=7
begin
insert into ts
select 1,20000002+@a,20000002+@a,1
set @a=@a+1
end
set @b=8
while @b<=12
begin
insert into ts
select 1,20000002+@b,20000002+@b,2
set @b=@b+1
end
set @c=13
while @c<=17
begin
insert into ts
select 2,20000003+@c,20000002+@c,3
set @c=@c+1
end
set @d=17
while @d<=21
begin
insert into ts
select 3,20000004+@d,20000004+@d,4
set @d=@d+1
end
select * from ts
drop table ts
---------眼睛花了 看错数字-----------控制号 条形码 登录号 系统号
1 20000001 20000001 1
1 20000002 20000001 2
2 20000003 20000002 3
3 20000004 20000004 4
1 20000005 20000005 1
1 20000006 20000006 1
1 20000007 20000007 1
1 20000008 20000008 1
1 20000009 20000009 1
1 20000010 20000010 2
1 20000011 20000011 2
1 20000012 20000012 2
1 20000013 20000013 2
1 20000014 20000014 2
2 20000016 20000015 3
2 20000017 20000016 3
2 20000018 20000017 3
2 20000019 20000018 3
2 20000020 20000019 3
3 20000021 20000021 4
3 20000022 20000022 4
3 20000023 20000023 4
3 20000024 20000024 4
3 20000025 20000025 4