新手有关问题：计算数值不在区间中的个数

--建表create table #tb([rev] varchar(200),[mon] money)go insert into #tb values              ('(A) $0  - $8,248 ',    0.00),('(A) $0  - $8,248 ',    55.08),('(A) $0  - $8,248 ',    90000.12),--不在，（A）('(A) $0  - $8,248',     0.00),('(A) $0  - $8,248',     0.00),('(C) $30,455  - $91,850',     48888.728),('(B) $8,249  - $30,419',     103079.28),--不在，（B）('(A) $0  - $8,248 ',    0.00),('(A) $0  - $8,248 ',    0.00),('(D) $91,929  - $16,905,130 ',    306454.166),('(B) $8,249  - $30,419',     13.38),--不在，（B）('(A) $0  - $8,248',     0.00),('(D) $91,929  - $16,905,130 ',    8989171792.96);--不在，（D）--要求：统计[mon]的数值不在[rev]对应的区间中的个数？/*结果为：[type]  [num] (A)      1 (B)      2 (C)      0 (D)      1  */

select type,sum(case when mon between num1 and num2 then 0 else 1 end) as numfrom(select left(rev,charindex(')',rev)) as type,  cast(replace(substring(rev,charindex('$',rev)+1,charindex('- $',rev)-charindex('$',rev)-1),',','') as dec(18,2)) as num1,  cast(replace(right(rev,charindex('$',reverse(rev))-1),',','')  as dec(18,2)) as num2,  monfrom #tb) tgroup by type/**type       num---------- -----------(A)        1(B)        2(C)        0(D)        1(4 行受影响)**/drop table #tb
------解决方案--------------------
SQL code
create table #tb([rev] varchar(200),[mon] money)go insert into #tb values              ('(A) $0  - $8,248 ',    0.00) insert into #tb values         ('(A) $0  - $8,248 ',    55.08) insert into #tb values         ('(A) $0  - $8,248 ',    90000.12)--不在，（A） insert into #tb values         ('(A) $0  - $8,248',     0.00) insert into #tb values         ('(A) $0  - $8,248',     0.00) insert into #tb values         ('(C) $30,455  - $91,850',     48888.728) insert into #tb values         ('(B) $8,249  - $30,419',     103079.28)--不在，（B） insert into #tb values         ('(A) $0  - $8,248 ',    0.00) insert into #tb values         ('(A) $0  - $8,248 ',    0.00) insert into #tb values         ('(D) $91,929  - $16,905,130 ',    306454.166) insert into #tb values         ('(B) $8,249  - $30,419',     13.38)--不在，（B） insert into #tb values         ('(A) $0  - $8,248',     0.00) insert into #tb values         ('(D) $91,929  - $16,905,130 ',    8989171792.96);--不在，（D）SELECT rev,SUM(CASE WHEN mon NOT BETWEEN mi AND ma THEN 1 ELSE 0 END) AS monFROM (    SELECT LEFT(rev,CHARINDEX(')',rev)) AS rev,        CAST(RTRIM(SUBSTRING(rev,CHARINDEX(')',rev)+1,CHARINDEX('-',rev)-CHARINDEX(')',rev)-1)) AS money) AS mi,        CAST(SUBSTRING(rev,CHARINDEX('-',rev)+1,8000) AS money) AS ma,mon    FROM #tb) AS AGROUP BY revDROP TABLE #tb
------解决方案--------------------
SQL code
create table #tb([rev] varchar(200),[mon] money)goinsert into #tb values              ('(A) $0  - $8,248 ',    0.00),('(A) $0  - $8,248 ',    55.08),('(A) $0  - $8,248 ',    90000.12),--不在，（A）('(A) $0  - $8,248',     0.00),('(A) $0  - $8,248',     0.00),('(C) $30,455  - $91,850',     48888.728),('(B) $8,249  - $30,419',     103079.28),--不在，（B）('(A) $0  - $8,248 ',    0.00),('(A) $0  - $8,248 ',    0.00),('(D) $91,929  - $16,905,130 ',    306454.166),('(B) $8,249  - $30,419',     13.38),--不在，（B）('(A) $0  - $8,248',     0.00),('(D) $91,929  - $16,905,130 ',    8989171792.96);--不在，（D）select t.le '[TYPE]',sum(t.st) '[NUM]'from(select substring(rev,patindex('%([A-Z])%',rev),3) le,case when mon betweencast(substring(rev,patindex('%) $%',rev)+3,charindex('-',rev)-patindex('%) $%',rev)-3) as money)andcast(substring(rev,patindex('%- $%',rev)+3,200) as money)then 0 else 1 end stfrom #tb) tgroup by t.le[TYPE] [NUM]------ -----------(A)    1(B)    2(C)    0(D)    1(4 row(s) affected)