如何写个存储过程

anny_gomine 2011-12-09 04:35:17

表一：
id name
1 a1
2 a1
3 a1
4 b1
5 b1
6 b1

表二：
id name code
1 a1 cnaa
2 b2 cnbb

执行后使表一的内容如下：
id name code
1 a1 cnaa1130a0001
2 a1 cnaa1130a0002
3 a1 cnaa1130a0003
4 b1 cnbb1130a0001
5 b1 cnbb1130a0002
6 b1 cnbb1130a0003
谢谢！

...全文

70 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

--小F-- 2011-12-09

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2011-12-09 14:00:14

-- Version:

--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 

--    Apr 22 2011 11:57:00 

--    Copyright (c) Microsoft Corporation

--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)

--

----------------------------------------------------------------

--> 测试数据：[a]

if object_id('[a]') is not null drop table [a]

go 

create table [a]([id] int,[name] varchar(20))

insert [a]

select 1,'a1' union all

select 2,'a1' union all

select 3,'a1' union all

select 4,'b1' union all

select 5,'b1' union all

select 6,'b1'

--> 测试数据：[b]

if object_id('[b]') is not null drop table [b]

go 

create table [b]([id] int,[name] varchar(20),[code] varchar(4))

insert [b]

select 1,'a1','cnaa' union all

select 2,'b1','cnbb'

--------------开始查询--------------------------

update

  a

set

  name=b.code

from

  a

join

(

select

  a.name,

  code=b.code+'1130a'+ltrim(right(1000+ROW_NUMBER()over(partition by a.name order by (select 1)),4))

 from

  a join b  

 on

  a.name=b.name

  )b

 on

  a.name=b.name 

  

  

select * from a

----------------结果----------------------------

/* 

(6 行受影响)



(2 行受影响)

id          name code

----------- ---- -----------------

1           a1   cnaa1130a1001

2           a1   cnaa1130a1002

3           a1   cnaa1130a1003

4           b1   cnbb1130a1001

5           b1   cnbb1130a1002

6           b1   cnbb1130a1003



(6 行受影响)





*/

--小F-- 2011-12-09

打赏
举报

----------------------------------------------------------------

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2011-12-09 14:00:14

-- Version:

--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 

--    Apr 22 2011 11:57:00 

--    Copyright (c) Microsoft Corporation

--    Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)

--

----------------------------------------------------------------

--> 测试数据：[a]

if object_id('[a]') is not null drop table [a]

go 

create table [a]([id] int,[name] varchar(2))

insert [a]

select 1,'a1' union all

select 2,'a1' union all

select 3,'a1' union all

select 4,'b1' union all

select 5,'b1' union all

select 6,'b1'

--> 测试数据：[b]

if object_id('[b]') is not null drop table [b]

go 

create table [b]([id] int,[name] varchar(2),[code] varchar(4))

insert [b]

select 1,'a1','cnaa' union all

select 2,'b1','cnbb'

--------------开始查询--------------------------

select

   a.id,a.name,

   code=b.code+'1130a'+ltrim(right(1000+ROW_NUMBER()over(partition by a.name order by (select 1)),4))

 from

   a join b 

 on

   a.name=b.name

----------------结果----------------------------

/* 

(6 行受影响)



(2 行受影响)

id          name code

----------- ---- -----------------

1           a1   cnaa1130a1001

2           a1   cnaa1130a1002

3           a1   cnaa1130a1003

4           b1   cnbb1130a1001

5           b1   cnbb1130a1002

6           b1   cnbb1130a1003



(6 行受影响)





*/

中国风 2011-12-09

打赏
举报

樓主T1表的Name數據不對

use Tempdb

go

--> --> 

 

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([id] int,[name] nvarchar(2))

Insert #T1

select 1,N'a1' union all

select 2,N'a1' union all

select 3,N'a1' union all

select 4,N'b2' union all

select 5,N'b2' union all

select 6,N'b2'

Go



 

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([id] int,[name] nvarchar(2),[code] nvarchar(4))

Insert #T2

select 1,N'a1',N'cnaa' union all

select 2,N'b2',N'cnbb'

Go



select 

a.*,b.code+'1130a'+ right(10000+(SELECT count(*) from #T1 where name=a.name and ID<=a.ID),4) AS code

from #T1 as a

inner join #T2  as b on a.name=b.name

/*

id	name	code

1	a1	cnaa1130a0001

2	a1	cnaa1130a0002

3	a1	cnaa1130a0003

4	b2	cnbb1130a0001

5	b2	cnbb1130a0002

6	b2	cnbb1130a0003

*/

中国风 2011-12-09

打赏
举报

use Tempdb

go

--> --> 

 

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([id] int,[name] nvarchar(2))

Insert #T1

select 1,N'a1' union all

select 2,N'a1' union all

select 3,N'a1' union all

select 4,N'b1' union all

select 5,N'b1' union all

select 6,N'b1'

Go



 

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([id] int,[name] nvarchar(2),[code] nvarchar(4))

Insert #T2

select 1,N'a1',N'cnaa' union all

select 2,N'b2',N'cnbb'

Go

select 

a.*,b.code+'1130a'+(select right(10000+count(*),4) from #T1 where name=a.name and ID=a.ID) AS code

from #T1 as a

inner join #T2  as b on a.name=b.name

/*

id	name	code

1	a1	cnaa1130a0001

2	a1	cnaa1130a0001

3	a1	cnaa1130a0001

*/

中国风 2011-12-09

打赏
举报



select 

a.*,b.code+'1130a'+(select right(10000+count(*),4) from t1 where name=a.name and ID=a.ID)

from t1 as a

inner join t2  as b on a.name=b.name