表A如下：
ID 范围
1 -10<结果<10
2 10>结果>-10
3 -10<结果
4 结果<10
5 结果>-10
6 10>结果
里面有且只有上面6种可能，我想是如果只有一个数字的，就直接取出，两个数字就取两个数字的平均值，结果应该这样：
ID 结果
1 0
2 0
3 -10
4 10
5 -10
6 10
求指点
------解决方案--------------------
- SQL code
create table #tb(id int,范围 varchar(100))insert #tbselect 1, '-10<结果<10'union select2 ,'10>结果>-10'union select3 ,'-10<结果'union select4 ,'结果<10'union select5,'结果>-10'union select6, '10>结果' select id, isnull(cast(substring(范围,1,CHARindex('结',范围)-2) as int),0)+isnull(cast(substring(范围,CHARindex('果',范围)+2,LEN(范围)-CHARindex('果',范围)) as int),0)from (select id, case when left(范围,1)='结' or right(范围,1)='果' then ' '+范围+' ' else 范围 end 范围 from #tb) a
------解决方案--------------------
- SQL code
if object_id('[TBA]') is not null drop table [TBA]gocreate table [TBA] (ID int,范围 nvarchar(18))insert into [TBA]select 1,'-10<结果<10' union allselect 2,'10>结果>-10' union allselect 3,'-10<结果' union allselect 4,'结果<10' union allselect 5,'结果>-10' union allselect 6,'10>结果'select * from [TBA]WITH TTAS(SELECT id,REPLACE(REPLACE(REPLACE(范围,'结果','.'),'>','.'),'<','.') AS colFROM TBA),T1AS(SELECT id, CASE WHEN LEFT(col,1) = '.' THEN '0.' ELSE '' END +col+CASE WHEN RIGHT(col,1) = '.' THEN '.0' ELSE '' END AS colFROM TT)SELECT id,CONVERT(INT,ISNULL(PARSENAME(col,1),0))+CONVERT(INT,ISNULL(PARSENAME(col,2),0))+CONVERT(INT,ISNULL(PARSENAME(col,3),0))+CONVERT(INT,ISNULL(PARSENAME(col,4),0)) AS NOFROM T1 /*id NO----------- -----------1 02 03 -104 105 -106 10(6 行受影响)*/
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')BEGIN DROP TABLE AENDGOCREATE TABLE A( ID INT, RG VARCHAR(100))GOINSERT INTO ASELECT 1, '-10<结果<10' UNIONSELECT 2, '10>结果>-10' UNIONSELECT 3, '-10<结果' UNIONSELECT 4, '结果<10' UNIONSELECT 5, '结果>-10' UNIONSELECT 6, '10>结果'GOWITH t0 AS( SELECT ID,REPLACE(RG,'结果','') AS RG FROM A)SELECT ID,CASE WHEN LEN(RG) - LEN(REPLACE(RG,'<','')) = 2 THEN CAST(LEFT(RG,CHARINDEX('<',RG) - 1) AS INT) + CAST(RIGHT(RG,LEN(RG) - CHARINDEX('<',RG) - 1) AS INT) WHEN LEN(RG) - LEN(REPLACE(RG,'<','')) = 1 THEN CAST(REPLACE(RG,'<','') AS INT) WHEN LEN(RG) - LEN(REPLACE(RG,'>','')) = 2 THEN CAST(LEFT(RG,CHARINDEX('>',RG) - 1) AS INT) + CAST(RIGHT(RG,LEN(RG) - CHARINDEX('>',RG) - 1) AS INT) WHEN LEN(RG) - LEN(REPLACE(RG,'>','')) = 1 THEN CAST(REPLACE(RG,'>','') AS INT) ENDFROM t0ID (No column name)1 02 03 -104 105 -106 10