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

SQL code

--建表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);--不在，—）--要求：统计[mon]的数值不在[rev]对应的区间中的个数？/*结果为：[type]  [num] (A)      1 (B)      2 (C)      0 (D)      1  */

SQL code

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);--不在，—）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);--不在，—）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) 
 
[解决办法]
SQL code
gocreate 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);--不在，—）select t.rev,isnull(COUNT(m.mon),0) mon from(select distinct LEFT(rev,4) rev from #tb)tleft join(select [rev],[mon] from (select *,rtrim(substring(replace([rev],',',''),CHARINDEX('$',replace([rev],',',''))+1,CHARINDEX('-',replace([rev],',',''))-CHARINDEX('$',replace([rev],',',''))-1)) as startmoney,right(replace([rev],',',''),len(replace([rev],',',''))-charindex('$',replace([rev],',',''),6))as endmoney from #tb)awhere [mon] not between CAST(startmoney as int) and CAST(endmoney as int))mon t.rev=LEFT(m.rev,4)group by t.rev/*rev    mon(A)     1(B)     2(C)     0(D)     1*/