code sn name 001 1 a1 001 2 a2 001 3 a3 002 1 b1 002 2 b2 003 1 c1 003 2 c2 003 3 c3 003 4 c4 \\\--select code,px=identity(int,1,1),name into tmp from tainsert into tb (name,sn,code) select name,px,code from tmp drop table tmp --or create table tb(sn int identity(1,1),name varchar(10),code varchar(10)) go insert tb(name,code) select name ,code form ta
insert into #temp(code char(3),b int,name varchar(6)) select a.code,b=@@identity,a.name from a group by a.code
--加个限制条件,可以防止重复导入 insert into newtb(code,sn,name) select code,sn,name from oldtb a where not exists(select 1 from newtb b where a.code=b.code and a.sn=b.sn)
insert into #temp(code char(3),b int,name varchar(6)) select a.code,b=identity(1,1),a.name from a group by a.code;
insert into tb2 select code,sn=(select count(*) from tb where code=a.code and name<=a.name),name from tb a
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([code] varchar(3),[name] varchar(2)) insert into #T select '001','a1' union all select '001','a2' union all select '001','a3' union all select '002','b1' union all select '002','b2' union all select '003','c1' union all select '003','c2' union all select '003','c3' union all select '003','c4' goif not object_id('Tempdb..#T2') is null drop table #T2 Go Create table #T2([code] varchar(3), [sn] int, [name] varchar(2))insert into #T2([code],[name],[sn]) select *, [sn]=row_number() over (partition by [code] order by [code]) from #Tselect * from #T2 /* Code sn Name 001 1 a1 001 2 a2 001 3 a3 002 1 b1 002 2 b2 003 1 c1 003 2 c2 003 3 c3 003 4 c4 */
问题是我表1的数据有几万条,怎样用 Create table #T([code] varchar(3),[name] varchar(2)) insert into #T select '001','a1' union all select '001','a2' union all select '001','a3' union all select '002','b1' union all select '002','b2' union all select '003','c1' union all select '003','c2' union all select '003','c3' union all select '003','c4' …
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([code] varchar(3),[name] varchar(2)) insert into #T select '001','a1' union all select '001','a2' union all select '001','a3' union all select '002','b1' union all select '002','b2' union all select '003','c1' union all select '003','c2' union all select '003','c3' union all select '003','c4' goif not object_id('Tempdb..#T2') is null drop table #T2 Go Create table #T2([code] varchar(3), [sn] int, [name] varchar(2))insert into #T2([code],[name],[sn]) select *, [sn]=right([name],len([name])-1) from #Tselect * from #T2
2005直接使用ROW_NUMBER()即可, 10楼的给出了相应的例子 :)
我们系统用的是sql2000,分发到多个单位. 不能用2005 还在什么方法吗?
几万条数据,count效率应该不是很低. -->生成测试数据
declare @tb table([code] nvarchar(3),[name] nvarchar(2)) Insert @tb select N'001',N'a1' union all select N'001',N'a2' union all select N'001',N'a3' union all select N'002',N'b1' union all select N'002',N'b2' union all select N'003',N'c1' union all select N'003',N'c2' union all select N'003',N'c3' union all select N'003',N'c4' Select [code],(select count(1) from @tb where [code] =t.[code] and [name] <=t.[name]) as sn, [name] from @tb t /* code sn name ---- ----------- ---- 001 1 a1 001 2 a2 001 3 a3 002 1 b1 002 2 b2 003 1 c1 003 2 c2 003 3 c3 003 4 c4 */
hery2002生成 /* code sn name ---- ----------- ---- 001 1 a1 001 2 a2 001 3 a3 002 1 b1 002 2 b2 003 1 c1 003 2 c2 003 3 c3 003 4 c4 */是可以我不理解下面这段语句: declare @tb table([code] nvarchar(3),[name] nvarchar(2)) Insert @tb select N'001',N'a1' union all select N'001',N'a2' union all select N'001',N'a3' union all select N'002',N'b1' union all select N'002',N'b2' union all select N'003',N'c1' union all select N'003',N'c2' union all select N'003',N'c3' union all select N'003',N'c4'如果我表1的数据是: ----------------- code name 201 ab1 201 ab2 201 ab3 202 bc1 202 bc2 ....... 808 abdc1 808 abdc2 --------------------declare @tb table([code] nvarchar(3),[name] nvarchar(2)) Insert @tb select N'201',N'ab1' union all select N'201',N'ab2' union all select N'201',N'ab3' union all select N'202',N'bc1' union all select N'202',N'bc2' union all ...... select N'808',N'abdc1' union all select N'808',N'abdc2'代码要上面那样吗? 那数据不定是多少条,那上面的代码就没用了吗?
那部分是做测试数据用的, 主要语句是 Select [code],(select count(1) from @tb where [code] =t.[code] and [name] <=t.[name]) as sn, [name] from @tb t 把里面对应的@tb换成你的实际的物理表就可以了.
Select [code],(select count(1) from @tb where [code] =t.[code] and [name] <=t.[name]) as sn, [name] from @tb t我实在是太菜了,还是不明白,帮我代入物理表吧 物理表是t_qyrk源表、t_children目标表 我代入的代码是: Select per_code,(select count(1) from t_qyrk where per_code =t_children.per_code and t_qyrk.name <=t_children.name) as sn, name from t_qyrk t_children/* per_code sn name -------- ------- ---- 450422107200000001 1 黎展中 450422107200000002 1 蒙小准 450422107200000027 1 黎亚妹 450422107200000028 1 黎贵雄 450422107200000029 1 杨彩梅 450422107200000030 1 黎贵雄 450422107200000031 1 杨清 450422107200000032 1 黎妹 */
Select per_code,(select count(1) from t_qyrk where per_code =t_children.per_code and t_qyrk.name <=t_children.name) as sn, name from t_qyrk t_children这是我套入的语句生成的数据/* per_code sn name -------- ------- ---- 450422107200000001 1 黎展中 450422107200000002 1 蒙小准 450422107200000027 1 黎亚妹 450422107200000028 1 黎贵雄 450422107200000029 1 杨彩梅 450422107200000030 1 黎贵雄 450422107200000031 1 杨清 450422107200000032 1 黎妹 */
001 1 a1
001 2 a2
001 3 a3
002 1 b1
002 2 b2
003 1 c1
003 2 c2
003 3 c3
003 4 c4
\\\--select code,px=identity(int,1,1),name into tmp from tainsert into tb (name,sn,code) select name,px,code from tmp
drop table tmp
--or
create table tb(sn int identity(1,1),name varchar(10),code varchar(10))
go
insert tb(name,code) select name ,code form ta
--加个限制条件,可以防止重复导入
insert into newtb(code,sn,name)
select code,sn,name from oldtb a
where not exists(select 1 from newtb b
where a.code=b.code and a.sn=b.sn)
select code,sn=(select count(*) from tb where code=a.code and name<=a.name),name
from tb a
从表1查询插入表2,SN字段自动生成ID
===============================
表1 字段code name
001 a1
001 a2
001 a3
002 b1
002 b2
003 c1
003 c2
003 c3
003 c4
code为主键
----------------------表2 字段
code sn name
001 1 a1
001 2 a2
001 3 a3
002 1 b1
002 2 b2
003 1 c1
003 2 c2
003 3 c3
003 4 c4 code、sn均为主键 SQL语句怎样写?
drop table #T
Go
Create table #T([code] varchar(3),[name] varchar(2))
insert into #T
select '001','a1' union all
select '001','a2' union all
select '001','a3' union all
select '002','b1' union all
select '002','b2' union all
select '003','c1' union all
select '003','c2' union all
select '003','c3' union all
select '003','c4'
goif not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([code] varchar(3), [sn] int, [name] varchar(2))insert into #T2([code],[name],[sn])
select *, [sn]=row_number() over (partition by [code] order by [code]) from #Tselect * from #T2
/*
Code sn Name
001 1 a1
001 2 a2
001 3 a3
002 1 b1
002 2 b2
003 1 c1
003 2 c2
003 3 c3
003 4 c4
*/
问题是我表1的数据有几万条,怎样用
Create table #T([code] varchar(3),[name] varchar(2))
insert into #T
select '001','a1' union all
select '001','a2' union all
select '001','a3' union all
select '002','b1' union all
select '002','b2' union all
select '003','c1' union all
select '003','c2' union all
select '003','c3' union all
select '003','c4' …
drop table #T
Go
Create table #T([code] varchar(3),[name] varchar(2))
insert into #T
select '001','a1' union all
select '001','a2' union all
select '001','a3' union all
select '002','b1' union all
select '002','b2' union all
select '003','c1' union all
select '003','c2' union all
select '003','c3' union all
select '003','c4'
goif not object_id('Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([code] varchar(3), [sn] int, [name] varchar(2))insert into #T2([code],[name],[sn])
select *, [sn]=right([name],len([name])-1) from #Tselect * from #T2
10楼的给出了相应的例子 :)
不能用2005
还在什么方法吗?
-->生成测试数据
declare @tb table([code] nvarchar(3),[name] nvarchar(2))
Insert @tb
select N'001',N'a1' union all
select N'001',N'a2' union all
select N'001',N'a3' union all
select N'002',N'b1' union all
select N'002',N'b2' union all
select N'003',N'c1' union all
select N'003',N'c2' union all
select N'003',N'c3' union all
select N'003',N'c4'
Select [code],(select count(1) from @tb where [code] =t.[code] and [name] <=t.[name]) as sn, [name] from @tb t
/*
code sn name
---- ----------- ----
001 1 a1
001 2 a2
001 3 a3
002 1 b1
002 2 b2
003 1 c1
003 2 c2
003 3 c3
003 4 c4
*/
/*
code sn name
---- ----------- ----
001 1 a1
001 2 a2
001 3 a3
002 1 b1
002 2 b2
003 1 c1
003 2 c2
003 3 c3
003 4 c4
*/是可以我不理解下面这段语句:
declare @tb table([code] nvarchar(3),[name] nvarchar(2))
Insert @tb
select N'001',N'a1' union all
select N'001',N'a2' union all
select N'001',N'a3' union all
select N'002',N'b1' union all
select N'002',N'b2' union all
select N'003',N'c1' union all
select N'003',N'c2' union all
select N'003',N'c3' union all
select N'003',N'c4'如果我表1的数据是:
-----------------
code name
201 ab1
201 ab2
201 ab3
202 bc1
202 bc2
.......
808 abdc1
808 abdc2
--------------------declare @tb table([code] nvarchar(3),[name] nvarchar(2))
Insert @tb
select N'201',N'ab1' union all
select N'201',N'ab2' union all
select N'201',N'ab3' union all
select N'202',N'bc1' union all
select N'202',N'bc2' union all
......
select N'808',N'abdc1' union all
select N'808',N'abdc2'代码要上面那样吗?
那数据不定是多少条,那上面的代码就没用了吗?
主要语句是
Select [code],(select count(1) from @tb where [code] =t.[code] and [name] <=t.[name]) as sn, [name] from @tb t
把里面对应的@tb换成你的实际的物理表就可以了.
物理表是t_qyrk源表、t_children目标表
我代入的代码是:
Select per_code,(select count(1) from t_qyrk where per_code =t_children.per_code and t_qyrk.name <=t_children.name) as sn, name from t_qyrk t_children/*
per_code sn name
-------- ------- ---- 450422107200000001 1 黎展中
450422107200000002 1 蒙小准
450422107200000027 1 黎亚妹
450422107200000028 1 黎贵雄
450422107200000029 1 杨彩梅
450422107200000030 1 黎贵雄
450422107200000031 1 杨清
450422107200000032 1 黎妹
*/
per_code sn name
-------- ------- ---- 450422107200000001 1 黎展中
450422107200000002 1 蒙小准
450422107200000027 1 黎亚妹
450422107200000028 1 黎贵雄
450422107200000029 1 杨彩梅
450422107200000030 1 黎贵雄
450422107200000031 1 杨清
450422107200000032 1 黎妹
*/