求序号转换SQL语句

[qq_27341511]

根据IDD序号计算CODEDETAILS
1\2\3 对应转换为A\B\C 以此类推
如下：

id idd code = codedetails
1 1 abc423 = abc423-A
1 2 abc423 = abc423-B
1 3 abc423 = abc423-C
1 4 abc423 = abc423-D
2 1 GDS12 = GDS12-A
2 2 GDS12 = GDS12-B

求语句！

[道玄希言]

case idd when 1 then 'A' when 2 then 'B' when 3 then 'C' . . . when 26 then Z else idd END

[江南小鱼]

select code + '-' + case iid%4 when 0 then 'D' when 1 then 'A' when 2 then 'B' when 3 then 'C' else cast(iid as varchar) end codedetails
from t --假设表名是t

[江南小鱼]

--1：A，2：B，3：C，4：D
--5：A，6：B，7：C，8：D
--。。。
select code + '-' + case iid%4 when 0 then 'D' when 1 then 'A' when 2 then 'B' when 3 then 'C' else cast(iid as varchar) end codedetails
from t --假设表名是t

[xiaoxiangqing]

楼上的正解.

[tcmakebest]

需求不完整啊,字母只有26个,IDD呢

[qq_27341511]

顶一下！顶一下！

[qq_27341511]

引用 1 楼 tcmakebest 的回复:

需求不完整啊,字母只有26个,IDD呢

不好意思，补充一下，序号不会超过26。 谢谢

[Tiger_Zhao]

函数CHAR()被忽视了！

WITH table1(idd,code) AS (
    SELECT number,'abc423'
      FROM master..spt_values
     WHERE type = 'p'
       AND number BETWEEN 1 AND 26
)
SELECT idd, code,
       code+'-'+CHAR(64+idd) codedetail
  FROM table1

        idd code   codedetail
----------- ------ ----------
          1 abc423 abc423-A
          2 abc423 abc423-B
          3 abc423 abc423-C
          4 abc423 abc423-D
          5 abc423 abc423-E
          6 abc423 abc423-F
          7 abc423 abc423-G
          8 abc423 abc423-H
          9 abc423 abc423-I
         10 abc423 abc423-J
         11 abc423 abc423-K
         12 abc423 abc423-L
         13 abc423 abc423-M
         14 abc423 abc423-N
         15 abc423 abc423-O
         16 abc423 abc423-P
         17 abc423 abc423-Q
         18 abc423 abc423-R
         19 abc423 abc423-S
         20 abc423 abc423-T
         21 abc423 abc423-U
         22 abc423 abc423-V
         23 abc423 abc423-W
         24 abc423 abc423-X
         25 abc423 abc423-Y
         26 abc423 abc423-Z

[qq_27341511]

谢谢各位！ 能否再补充一下需求： 如果只有一个CODE的IDD只有一个时，不更新，怎么改这个触发器呢？ CREATE TRIGGER upt_sales ON sales for INSERT,UPDATE AS update sales set sales .codedetails =inserted.Code +'-' + case inserted.idd when 1 then 'A' when 2 then 'B' when 3 then 'C' when 4 then 'D' when 5 then 'E' when 6 then 'F' when 7 then 'G' when 8 then 'H' when 9 then 'I' when 10 then 'J' when 11 then 'K' when 12 then 'L' when 13 then 'M' when 14 then 'N' when 15 then 'O' when 16 then 'P' when 17 then 'Q' when 18 then 'R' when 19 then 'S' when 20 then 'T' when 21 then 'U' when 22 then 'V' when 23 then 'W' when 24 then 'X' when 25 then 'Y' when 26 then 'Z' ELSE 'AA' END from sales,inserted WHERe sales.AutoID =inserted.AutoID

[qq_27341511]

即 idd code codedetail ----------- ------ ---------- 1 abc4 abc4 1 abc423 abc423-A 2 abc423 abc423-B

[Tiger_Zhao]

加条件啊。

AND EXISTS(SELECT * FROM sales WHERE code = inserted.code AND idd <> inserted.idd)

不过这样很容易死锁啊，为什么不在前台控制，一次更新一组code。

[yooq_csdn]

再建一个 IDD 和 字母的对应表不好吗，关联更新一下就好了。