create table table1(COL1 int,COL2 varchar(10),COL3 int)
insert table1
select 1,'小于1',0 union all
select 2,'1-3',3 union all
select 3,'4-7',5 union all
select 4,'7-10',10 union all
select 5,'10以上',20create table table2(COL1 int,COL2 datetime)
insert table2
select 1,'2014-07-07' union all
select 2,'2014-01-01' union all
select 3,'2013-06-01' union all
select 4,'2010-01-01' union all
select 5,'2005-01-01' union all
select 6,'2000-01-01'with test as(
select COL1=case when COL2='小于1' then -99999
when COL2='10以上' then 11
else SUBSTRING(COL2,1,CHARINDEX('-',COL2)-1)
end,
COL2=case when COL2='小于1' then 0
when COL2='10以上' then 99999
else SUBSTRING(COL2,CHARINDEX('-',COL2)+1,LEN(COL2))
end,
COL3
from table1
)
select t2.COL1,t.COL3
from table2 t2
inner join test t
on DATEDIFF(YEAR,t2.COL2,GETDATE()) between t.COL1 and t.COL2
/*
1 0
2 0
3 3
4 5
5 10
6 20
*/
--alter table table1 add [max] int
update table1 set
[min]=
case when col2='小于1' then '-99999'
when col2='1-3' then 1
when col2='4-7' then 4
when col2='7-10' then 7
when col2='10以上' then 10 end,
[max]=
case when col2='小于1' then '1'
when col2='1-3' then 3
when col2='4-7' then 7
when col2='7-10' then 10
when col2='10以上' then '99999' end
select case
when
[min]<col3 and col3>[max] and [min]!=-99999 and [max]!=99999 then 1
when
[min]=-99999 and col3<=[min] then 1
when
[max]=99999 and col3>=[max] then 1 else 0 end
from table1
建议改下表结构。