/*我的测试代码如下,你再测试一下,测试通过再给我加分,我总觉得你的这个问题用函数最合适*/drop table cust
go
create table cust (用户名 char(8),行业 varchar(20),金额 int)
go
insert cust values ('00000004','3',300)
insert cust values ('00000001','1',50)
insert cust values ('00000002','2',150)
go
drop function getid
go
create function getid(@name char(8))
returns char(8)
as
begin
declare @sum int
declare @kind varchar(20)
declare @index int
declare @id varchar(8)
select @sum=金额,@kind =行业 from cust where 用户名 = @name
select @id = case when @sum >100 then 'A' else 'B' end
select @id = @id + case when @kind ='1' then 'F'
when @kind ='2' then 'G'
else 'Z' end
select @index = count(*)+1 from cust where 金额 > @sum
select @id = @id + substring(convert(char(8),@index*0.000001),3,8)
return (convert(char(8),@id))
end
go
select * from cust
go
alter table cust add id char(8)
go
update cust set id=dbo.getid(用户名)
go
select * from cust
go
go
create table cust (用户名 char(8),行业 varchar(20),金额 int)
go
insert cust values ('00000004','3',300)
insert cust values ('00000001','1',50)
insert cust values ('00000002','2',150)
go
drop function getid
go
create function getid(@name char(8))
returns char(8)
as
begin
declare @sum int
declare @kind varchar(20)
declare @index int
declare @id varchar(8)
select @sum=金额,@kind =行业 from cust where 用户名 = @name
select @id = case when @sum >100 then 'A' else 'B' end
select @id = @id + case when @kind ='1' then 'F'
when @kind ='2' then 'G'
else 'Z' end
select @index = count(*)+1 from cust where 金额 > @sum
select @id = @id + substring(convert(char(8),@index*0.000001),3,8)
return (convert(char(8),@id))
end
go
select * from cust
go
alter table cust add id char(8)
go
update cust set id=dbo.getid(用户名)
go
select * from cust
go
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货