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

Question

--建表
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  
*/

Answer 1

select * from 表名 where 字段 not between ... and ...

Answer 2

select type,sum(case when mon between num1 and num2 then 0 else 1 end) as num
from
(
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,
  mon
from #tb
) t
group by type
/**
type       num
---------- -----------
(A)        1
(B)        2
(C)        0
(D)        1(4 行受影响)
**/drop table #tb

Answer 3

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 mon
FROM (
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 A
GROUP BY rev
DROP TABLE #tb

Answer 4

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 between
cast(substring(rev,patindex('%) $%',rev)+3,charindex('-',rev)-patindex('%) $%',rev)-3) as money)
and
cast(substring(rev,patindex('%- $%',rev)+3,200) as money)
then 0 else 1 end st
from #tb) t
group by t.le
[TYPE] [NUM]
------ -----------
(A)    1
(B)    2
(C)    0
(D)    1(4 row(s) affected)

Answer 5

go
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）
select t.rev,isnull(COUNT(m.mon),0) mon from(
select distinct LEFT(rev,4) rev from #tb)t
left 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)a
where [mon] not between CAST(startmoney as int) and CAST(endmoney as int))m
on t.rev=LEFT(m.rev,4)
group by t.rev/*
rev mon
(A)  1
(B)  2
(C)  0
(D)  1
*/