A表
id startNum endNum
1 3 6
2 13 19
3 22 25B表
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 ,想得到表CC表
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
id startNum endNum
1 3 6
2 13 19
3 22 25B表
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 ,想得到表CC表
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
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 ,25create 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
*/
select id,number as singleNum ,(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 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
*/
aa.number,
CASE WHEN aa.number = bb.defeasanceNum THEN 'TRUE'
ELSE 'FALSE'
END AS isDefeasance
FROM ( SELECT id,
number
FROM ( SELECT number
FROM MASTER..spt_values
WHERE TYPE = 'p'
) a,
t1 b
WHERE b.startNum <= a.number
AND b.endNum >= a.number
) aa
LEFT JOIN t2 bb ON aa.id = bb.id
用这个
SELECT aa.id,
aa.number,
(case when exists(select 1 from t2 bb where bb.defeasanceNum=aa.number)
then 'true' else 'false' end) as isDefeasance
FROM ( SELECT id,
number
FROM ( SELECT number
FROM MASTER..spt_values
WHERE TYPE = 'p'
) a,
t1 b
WHERE b.startNum <= a.number
AND b.endNum >= a.number
) aa
AS (SELECT 1 AS row
UNION ALL
SELECT row + 1
FROM NUMBER
WHERE row < 1000)
SELECT a.id,
NUMBER.row AS NUMBER,
CASE
WHEN b.defeasancenum IS NULL THEN 'fasle'
ELSE 'true'
END AS isdefeasance
FROM NUMBER
INNER JOIN a
ON NUMBER.row BETWEEN a.startnum AND a.endnum
LEFT JOIN b
ON NUMBER.row = b.defeasancenum
OPTION (MAXRECURSION 0)
WITH NUMBER(row)
AS (SELECT 1 AS row
UNION ALL
SELECT row + 1
FROM NUMBER
WHERE row < 1000)
SELECT a.id,
NUMBER.row AS NUMBER,
CASE
WHEN b.defeasanceNum IS NULL THEN 'fasle'
ELSE 'true'
END AS isdefeasance
FROM NUMBER
INNER JOIN a
ON NUMBER.row BETWEEN a.startnum AND a.endnum
LEFT JOIN b
ON NUMBER.row = b.defeasanceNum
OPTION (MAXRECURSION 0)