求批量更新表中某字段的SQL话语
求批量更新表中某字段的SQL语句
ID NAME
1001 CG14*13.5-85/31-37/113*23W
1002 CG6.5*5.4-190/31-37/248*33W
1003 CG5*5-105/30-32/103.5*10.5V+W
1004 CG5*5-105/30-33/103*10.5
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)
1006 CG6.5*5.4-190/31-37/114.5*23P
想得到下面的结果
ID NAME
1001 CG14*13.5-85/31-37/113*23W/二
1002 CG6.5*5.4-190/31-37/248*33W/二
1003 CG5*5-105/30-32/103.5*10.5V+W/三
1004 CG5*5-105/30-33/103*10.5/三
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)/二
1006 CG6.5*5.4-190/31-37/114.5*23P/
当两个//间的绝对值小于5时,在对应的NAME后加/三,大于5加/二
例如:CG14*13.5-85/31-37/113*23W
31-37的绝对值是6,大于5,所以结果为:CG14*13.5-85/31-37/113*23W/二
CG5*5-105/30-32/103.5*10.5V+W
30-32的绝对值2,小于5 所以结果为:CG5*5-105/30-32/103.5*10.5V+W/三
------解决方案--------------------
字段截取 substring 截取值再用case when 比较
------解决方案--------------------
ID NAME
1001 CG14*13.5-85/31-37/113*23W
1002 CG6.5*5.4-190/31-37/248*33W
1003 CG5*5-105/30-32/103.5*10.5V+W
1004 CG5*5-105/30-33/103*10.5
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)
1006 CG6.5*5.4-190/31-37/114.5*23P
想得到下面的结果
ID NAME
1001 CG14*13.5-85/31-37/113*23W/二
1002 CG6.5*5.4-190/31-37/248*33W/二
1003 CG5*5-105/30-32/103.5*10.5V+W/三
1004 CG5*5-105/30-33/103*10.5/三
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)/二
1006 CG6.5*5.4-190/31-37/114.5*23P/
当两个//间的绝对值小于5时,在对应的NAME后加/三,大于5加/二
例如:CG14*13.5-85/31-37/113*23W
31-37的绝对值是6,大于5,所以结果为:CG14*13.5-85/31-37/113*23W/二
CG5*5-105/30-32/103.5*10.5V+W
30-32的绝对值2,小于5 所以结果为:CG5*5-105/30-32/103.5*10.5V+W/三
------解决方案--------------------
字段截取 substring 截取值再用case when 比较
------解决方案--------------------
- SQL code
Declare @Tbl Table (ID int, NAME varchar(50)) Insert Into @Tbl Select 1001, 'CG14*13.5-85/31-37/113*23W' Union All Select 1002, 'CG6.5*5.4-190/31-37/248*33W' Union All Select 1003, 'CG5*5-105/30-32/103.5*10.5V+W' Union All Select 1004, 'CG5*5-105/30-33/103*10.5' Union All Select 1005, 'CG6.5*5.3-125/31-37/104*9.8B(V+W)' Union All Select 1006, 'CG6.5*5.4-190/31-37/114.5*23P' ;With Tbl(ID, Num1, Num2) As( Select ID, CAST(SUBSTRING(SUBSTRING(NAME, CHARINDEX('/', NAME) + 1, 5), 1, 2) As Int) , CAST(SUBSTRING(SUBSTRING(NAME, CHARINDEX('/', NAME) + 1, 5), 4, 2) As Int) From @Tbl ) Update A Set A.Name = A.Name + Case When ABS(B.Num1-B.Num2) > 5 Then '/二' Else '/三' End From @Tbl A Inner Join Tbl B On A.ID = B.ID Select * From @Tbl /* ID NAME ----------- -------------------------------------------------- 1001 CG14*13.5-85/31-37/113*23W/二 1002 CG6.5*5.4-190/31-37/248*33W/二 1003 CG5*5-105/30-32/103.5*10.5V+W/三 1004 CG5*5-105/30-33/103*10.5/三 1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)/二 1006 CG6.5*5.4-190/31-37/114.5*23P/二 */