你那样转换是有问题的,去建一个临时表,结构如下id -- wordid1 ----- A 2 ----- B .... 26----- Z你的表id字段确定是字母么,或者别更新直接查询吧!select a.*,ltrim(a.id)+b.wordid from a join b on a.id%26 = b.id
create table tb(id int,score int) insert into tb select 1,10 union all select 2,34 goselect *,char(ascii('A')+(id-1)%26) as word from tbdrop table tb/****************id score word ----------- ----------- ---- 1 10 A 2 34 B(2 行受影响)
不太明白,我的意思是最后变成 ID SCORE 1A 50 2B 50 3C 50 这个样子
select ascii('A') --> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([id] int,[score] int) insert [test] select 1,50 union all select 2,50 union all select 3,50select LTRIM([id])+CHAR(64+id) as id,[score] from test/* id score 1A 50 2B 50 3C 50 */
最后一句什么意思select LTRIM([id])+CHAR(64+id) as id,[score] from test 报错
楼主用的是什么数据库?是SQL SERVER吗?哪个版本?还是access?
--SQL SERVER 里面是没有问题的 --ltrim是去掉左空格,这里是将int 隐式转换成 varchar declare @T table([id] int,[score] int) insert @T select 1,50 union all select 2,50 union all select 3,50select ltrim(id)+char(id+64) as id,[score] from @T /* id score ------------- ----------- 1A 50 2B 50 3C 50 */
2 ----- B
....
26----- Z你的表id字段确定是字母么,或者别更新直接查询吧!select a.*,ltrim(a.id)+b.wordid
from a join b on a.id%26 = b.id
create table tb(id int,score int)
insert into tb
select 1,10 union all
select 2,34
goselect *,char(ascii('A')+(id-1)%26) as word
from tbdrop table tb/****************id score word
----------- ----------- ----
1 10 A
2 34 B(2 行受影响)
ID SCORE
1A 50
2B 50
3C 50
这个样子
select ascii('A')
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([id] int,[score] int)
insert [test]
select 1,50 union all
select 2,50 union all
select 3,50select LTRIM([id])+CHAR(64+id) as id,[score] from test/*
id score
1A 50
2B 50
3C 50
*/
--SQL SERVER 里面是没有问题的
--ltrim是去掉左空格,这里是将int 隐式转换成 varchar
declare @T table([id] int,[score] int)
insert @T
select 1,50 union all
select 2,50 union all
select 3,50select ltrim(id)+char(id+64) as id,[score] from @T
/*
id score
------------- -----------
1A 50
2B 50
3C 50
*/
SELECT @temp_int = @temp_int + 1
SELECT @temp_char = CHAR(@temp_int)
这样就可以 结合 游标