直接重做除0009的地区记录: 在sqlplus中执行: set heading off set feedback off set term off spool a.sql SELECT DISTINCT 'insert into A (zoneno,value) select ' || CHR (39) || zoneno || CHR (39) || ',value from A where zoneno= ''0009'' ' FROM a WHERE zoneno <> '0009' spool off exit; / DELETE FROM a WHERE zoneno <> '0009'; @a.sql; commit;
insert into 表A(zoneno,value) select new.zoneno,new.id from (select a.zoneno,b.id from (select distinct zoneno from 表A) a, (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11) b) new left join 表A a on new.zoneno=a.zoneno where a.zoneno is null and a.value is null --或者 insert into 表A(zoneno,value) select new.zoneno,new.id from (select a.zoneno,b.id from (select distinct zoneno from 表A) a, (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11) b) new where not exists(select 1 from 表A where zoneno=new.zoneno and value=new.value)
先删除原有的数据 delete adeclare @zoneon_count int set @zoneon_count=1 while @zoneon_count<=10 begindeclare @value_count int set @value_count=1 while @value_count<=11 begin--位数不够补0 declare @zoneon_count_0 varchar(4) set @zoneon_count_0= case len(@zoneon_count) when 1 then '000'+convert(varchar(4),@zoneon_count) when 2 then '00'+convert(varchar(4),@zoneon_count) when 3 then '0'+convert(varchar(4),@zoneon_count) else convert(varchar(4),@zoneon_count) endinsert a (zoneno,value) values(@zoneon_count_0,@value_count)--值累加 set @value_count=@value_count+1 end-- 区号累加 set @zoneon_count=@zoneon_count+1 end
把while @zoneon_count<=10 改成1000即可以满足"有1000个地区"
create table a表 (zoneno varchar(10),value int) insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 create table b (id int IDENTITY (1,1)NOT NULL,zoneno varchar(10),value int) insert into b(zoneno,value) select zoneno,0 from a表 drop table a表 select M.* into a表 from (select T.zoneno,(select count(*) from b where b.id<=T.id) as value from b T) M ALTER TABLE a表 DROP COLUMN id drop table bselect * from a表
sorry!上面不对! create table a表 (zoneno varchar(10),value int) insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 insert a表 select '0001',0 create table b (id int IDENTITY (1,1)NOT NULL,zoneno varchar(10),value int) insert into b(zoneno,value) select zoneno,0 from a表 drop table a表 select M.* into a表 from (select T.zoneno,(select count(*) from b where b.id<=T.id and b.zoneno=T.zoneno) as value from b T) M ALTER TABLE a表 DROP COLUMN id drop table bselect * from a表
update 表名 set value1=b.value1,value2=b.value2...... from 表名 A left join 表名 B on b.地区号='0009'--不过我想问一下, --如果像你这样处理,那为什么要建这么多的地区号,一个不就行啦,就‘0009’就够啦
搞定了 insert into A select t1.zoneno,t2.value from (select zoneno from A group by zoneno) t1, (select value from A where zoneno='0009') t2 where not exists(select 1 from A where zoneno=t1.zoneno and value=t2.value)
在sqlplus中执行:
set heading off
set feedback off
set term off
spool a.sql
SELECT DISTINCT 'insert into A (zoneno,value) select '
|| CHR (39)
|| zoneno
|| CHR (39)
|| ',value from A where zoneno= ''0009'' '
FROM a
WHERE zoneno <> '0009'
spool off
exit;
/
DELETE FROM a WHERE zoneno <> '0009';
@a.sql;
commit;
insert into 表A(zoneno,value)
select new.zoneno,new.id
from (select a.zoneno,b.id
from (select distinct zoneno from 表A) a,
(select 1 as id union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9 union select 10 union select 11) b) new
left join 表A a on new.zoneno=a.zoneno
where a.zoneno is null and a.value is null
--或者
insert into 表A(zoneno,value)
select new.zoneno,new.id
from (select a.zoneno,b.id
from (select distinct zoneno from 表A) a,
(select 1 as id union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9 union select 10 union select 11) b) new
where not exists(select 1 from 表A where zoneno=new.zoneno and value=new.value)
delete adeclare @zoneon_count int
set @zoneon_count=1
while @zoneon_count<=10
begindeclare @value_count int
set @value_count=1
while @value_count<=11
begin--位数不够补0
declare @zoneon_count_0 varchar(4)
set @zoneon_count_0=
case len(@zoneon_count)
when 1 then '000'+convert(varchar(4),@zoneon_count)
when 2 then '00'+convert(varchar(4),@zoneon_count)
when 3 then '0'+convert(varchar(4),@zoneon_count)
else convert(varchar(4),@zoneon_count)
endinsert a (zoneno,value) values(@zoneon_count_0,@value_count)--值累加
set @value_count=@value_count+1
end-- 区号累加
set @zoneon_count=@zoneon_count+1
end
改成1000即可以满足"有1000个地区"
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
create table b (id int IDENTITY (1,1)NOT NULL,zoneno varchar(10),value int)
insert into b(zoneno,value) select zoneno,0 from a表
drop table a表
select M.* into a表 from (select T.zoneno,(select count(*) from b where b.id<=T.id) as value from b T) M
ALTER TABLE a表 DROP COLUMN id
drop table bselect * from a表
create table a表 (zoneno varchar(10),value int)
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
insert a表 select '0001',0
create table b (id int IDENTITY (1,1)NOT NULL,zoneno varchar(10),value int)
insert into b(zoneno,value) select zoneno,0 from a表
drop table a表
select M.* into a表 from (select T.zoneno,(select count(*) from b where b.id<=T.id and b.zoneno=T.zoneno) as value from b T) M
ALTER TABLE a表 DROP COLUMN id
drop table bselect * from a表
是这样的,我为了描述的方便只写了两个字段,其实后有其他的几个字段,我希望是能够通过select方式把0009地区除了地区号以外的所有字段值赋给其他地区的相应字段
如果009地区的所有字段值为:
0009 1 value**** value**** value****
0009 2 value**** value**** value****
0009 3 value**** value**** value****
0009 4 value**** value**** value****
0009 5 value**** value**** value****
0009 6 value**** value**** value****
0009 7 value**** value**** value****
0009 8 value**** value**** value****
0009 9 value**** value**** value****
0009 10 value**** value**** value****
0009 11 value**** value**** value****
那么其他地区的相应字段(除了第一个字段地区号)都是上面的后面4个字段值
多谢各位了
update 表名 set value1=b.value1,value2=b.value2......
from 表名 A
left join 表名 B
on b.地区号='0009'--不过我想问一下,
--如果像你这样处理,那为什么要建这么多的地区号,一个不就行啦,就‘0009’就够啦
insert into A
select
t1.zoneno,t2.value
from
(select zoneno from A group by zoneno) t1,
(select value from A where zoneno='0009') t2
where
not exists(select 1 from A where zoneno=t1.zoneno and value=t2.value)