日期:2014-05-18 浏览次数:20627 次
create table a(id int ,startNum int, endNum int)
insert a
select 1 ,3 ,6 union all
select 2 ,13 ,19 union all
select 3 ,22 ,25
create table b(id int ,defeasanceNum int)
insert b
select 1 ,5 union all
select 2 ,14 union all
select 3 ,16 union all
select 4 ,24
select *,(case when exists(select 1 from b where b.defeasanceNum=tb.number) 
          then 'true' else 'false' end) as isDefeasance
from 
(select a.id,number from a,master..spt_values where type='p'
and number>=a.startNum and number<=a.endNum) tb
select * from a
/*
id          number      isDefeasance 
----------- ----------- ------------ 
1           3           false
1           4           false
1           5           true
1           6           false
2           13          false
2           14          true
2           15          false
2           16          true
2           17          false
2           18          false
2           19          false
3           22          false
3           23          false
3           24          true
3           25          false
*/