日期:2014-05-18  浏览次数:20425 次

连续号码拆分
A表
id startNum endNum
1 3 6
2 13 19
3 22 25

B表
id defeasanceNum
1 5
2 14
3 16
4 24
如上两个表,表A每一行表示一段数字,例如id=1,是3到6,id=2是从13到19,注意表A的id和表B的id,无任何关系,不是外键关系,就是个编号而已,表B中的defeasanceNum,表示A 表中作废的数字,根据表A和表B ,想得到表C

C表
a_id singleNum 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

作废的号,isDefeasance显示 true
 

------解决方案--------------------
SQL code

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