日期:2014-05-18 浏览次数:20584 次
--表中记录 /* Num Count2 Code 92126 200 '100202' 94752 100 '100202' 19 200 '100201' 20 100 '100201' */ --希望得到的查询结果,即希望Num>90000分拆成相应的几条 /* Num Count2 Code 21 200 '100202' 22 200 '100202' 23 200 '100202' 24 200 '100202' 25 200 '100202' 26 200 '100202' 47 100 '100202' 48 100 '100202' 49 100 '100202' 50 100 '100202' 51 100 '100202' 52 100 '100202' 19 200 '100201' 20 100 '100201' */
--参考: select b.Number Num,a.count2,a.code from tb a left join master..spt_values b on a.Num>90000 and b.type='p' and b.number between substring(rtrim(a.Num),2,2) and right(a.Num,2) union all select Num,count2,code from tb where Num<=900000
------解决方案--------------------
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-08-05 14:08:24 --------------------------------- --> 生成测试数据表:tb If not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([Num] int,[Count2] int,[Code] varchar(10)) Insert tb Select 92126,200,'100202' union all Select 94752,100,'100202' union all Select 19,200,'100201' union all Select 20,100,'100201' Go --Select * from tb -->SQL查询如下: select b.number num,a.[Count2],a.code from tb a join master..spt_values b on b.type='p' and a.num>90000 and b.number>=(a.num-90000)/100 and b.number<=(a.num-90000)%100 union all select * from tb where num<90000 /* num Count2 code ----------- ----------- ---------- 21 200 100202 22 200 100202 23 200 100202 24 200 100202 25 200 100202 26 200 100202 47 100 100202 48 100 100202 49 100 100202 50 100 100202 51 100 100202 52 100 100202 19 200 100201 20 100 100201 (14 行受影响) */