重复插入表中已有的记录，但是序号字段值改成其他值，重量值改成负数

Question

如原表结构如下：
xh                                                    zl             zh
232910C011100000001001                              33.44             1
232910C011100000001002                              30.02             1
232910C011100000001003                              28.12             1重复插入表中zh值相同的记录，插入后，新的xh变成某种随机值，前几位都相同，其最后1位是16进制按大小排序。zl字段值重复插入后变成负数，重复插入后，表内容如下：
xh                                                    zl        zh232910C011100000001001                              33.44        1
232910C011100000001002                              30.02        1
232910C011100000001003                              28.12        1
B329B391AC3944C28E7E89465265C318                   -33.44        2
B329B391AC3944C28E7E89465265C319                   -30.02        2
B329B391AC3944C28E7E89465265C31A                   -28.12        2该怎么来写这个插入语句呢？
插入后，同一个zh的记录的zh值只是最后一位不同，不同zh值的记录的xh随机值不能相同，

Answer 1

create table t15(xh varchar(30), zl decimal(9,2), zh int)insert into t15
select '232910C011100000001001', 33.44, 1 union all
select '232910C011100000001002', 30.02, 1 union all
select '232910C011100000001003', 28.12, 1select * from t15xh                             zl                                      zh
------------------------------ --------------------------------------- -----------
232910C011100000001001         33.44                                   1
232910C011100000001002         30.02                                   1
232910C011100000001003         28.12                                   1insert into t15
select xh,zl,2
from t15 where zh=1select * from t15xh                             zl                                      zh
------------------------------ --------------------------------------- -----------
232910C011100000001001         33.44                                   1
232910C011100000001002         30.02                                   1
232910C011100000001003         28.12                                   1
232910C011100000001001         33.44                                   2
232910C011100000001002         30.02                                   2
232910C011100000001003         28.12                                   2

Answer 2

看不到有什麼作用，生成規則是怎樣也是不清不楚?--GUID =SELECT REPLACE(NEWID(),'-','')B329B391AC3944C28E7E89465265C318 -33.44 2
B329B391AC3944C28E7E89465265C319 -30.02 2
B329B391AC3944C28E7E89465265C31A -28.12 2

Answer 3

只要求这个条件了：
重复插入表中zh值相同的记录，插入后，新的xh变成某种随机值，前几位都相同，其最后1位是16进制按大小排序。zl字段值重复插入后变成负数

Answer 4

插入前是这样：
xh                             zl                                      zh
------------------------------ --------------------------------------- -----------
232910C011100000001001         33.44                                   1
232910C011100000001002         30.02                                   1
232910C011100000001003         28.12                                   1插入后，要是这样：新的xh值，前面是随机，最后一位是16进制的数大小排序xh                                     zl                                      zh
---------------------------------- --------------------------------------- -----------
232910C011100000001001                 33.44                                   1
232910C011100000001002                 30.02                                   1
232910C011100000001003                 28.12                                   1
B329B391AC3944C28E7E89465265C318      -33.44                                   2  
B329B391AC3944C28E7E89465265C319      -30.02                                   2
B329B391AC3944C28E7E89465265C31A      -28.12                                   2

Answer 5

猜1個
if exists(select 1 from table1 where  zl=@zl and zh=@zh)
select @xh=REPLACE(NEWID(),'-',''),@zl=-@zl
insert into Table1(xh,zl,zh) values(@xh,@zl,@zh)

Answer 6

B329B391AC3944C28E7E89465265C318 --8是怎樣計算的,為什麼不是0不是1?

Answer 7

重新发下，不然看不清
插入前是这样：
xh                             zl                                  zh
------------------------------ ------------------------------------ -----------
232910C011100000001001        33.44                                 1    
232910C011100000001002        30.02                                 1
232910C011100000001003        28.12                                 1插入后，要是这样：新的xh值，前面是随机，最后一位是16进制的数大小排序xh                                    zl                                   zh
---------------------------------- --------------------------------------- -----------
232910C011100000001001               33.44                               1
232910C011100000001002               30.02                               1
232910C011100000001003               28.12                               1
B329B391AC3944C28E7E89465265C318     -33.44                              2   
B329B391AC3944C28E7E89465265C319     -30.02                              2
B329B391AC3944C28E7E89465265C31A     -28.12                              2

Answer 8

B329B391AC3944C28E7E89465265C31--前面部分一批插入時也是要相同還是隨機