22,206
社区成员
发帖
与我相关
我的任务
分享
with test_1108 as
(
select 1 id,'000-1001' num,1001 conntect,100.00 decmial
union all
select 2 , '000-1002-05', 1002 , 200.00
union all
select 3 , '000-1006' , 1003 , 300.00
UNION all
select 3 , '000-1007-10' , 1005 , 300.00
union all
select 6 , '000-1011-16', 1006 , 600.00
)
SELECT t1.id,c.prefix+RIGHT('000'+LTRIM(sv.number),3) AS num
,t1.conntect,CASE WHEN sv.number=c.f THEN t1.decmial ELSE NULL END AS decmial
FROM test_1108 t1
CROSS APPLY(VALUES(SUBSTRING(t1.num,1,5)
,CONVERT(INT,SUBSTRING(t1.num,6,3))
,CASE WHEN LEN(t1.num)>10 THEN CONVERT(INT,SUBSTRING(t1.num,10,100)) ELSE CONVERT(INT,SUBSTRING(t1.num,6,3)) END
)) c(prefix,f,t)
LEFT JOIN master.dbo.spt_values AS sv ON sv.type='P' AND sv.number BETWEEN c.f AND c.t
+----+----------+----------+---------+
| id | num | conntect | decmial |
+----+----------+----------+---------+
| 1 | 000-1001 | 1001 | 100 |
| 2 | 000-1002 | 1002 | 200 |
| 2 | 000-1003 | 1002 | NULL |
| 2 | 000-1004 | 1002 | NULL |
| 2 | 000-1005 | 1002 | NULL |
| 3 | 000-1006 | 1003 | 300 |
| 3 | 000-1007 | 1005 | 300 |
| 3 | 000-1008 | 1005 | NULL |
| 3 | 000-1009 | 1005 | NULL |
| 3 | 000-1010 | 1005 | NULL |
| 6 | 000-1011 | 1006 | 600 |
| 6 | 000-1012 | 1006 | NULL |
| 6 | 000-1013 | 1006 | NULL |
| 6 | 000-1014 | 1006 | NULL |
| 6 | 000-1015 | 1006 | NULL |
| 6 | 000-1016 | 1006 | NULL |
+----+----------+----------+---------+
with test_1108 as
(
select 1 id,'000-1001' num,1001 conntect,100.00 decmial
union all
select 2 , '000-1002-05', 1002 , 200.00
union all
select 3 , '000-1006' , 1003 , 300.00
union all
select 6 , '000-1011-16', 1006 , 600.00
)
SELECT t1.id,c.prefix+RIGHT('000'+LTRIM(c.f),3)+ISNULL('-000'+LTRIM(NULLIF(sv.number,c.f)),'') AS num
,t1.conntect,CASE WHEN sv.number=c.f THEN t1.decmial ELSE NULL END AS decmial
FROM test_1108 t1
CROSS APPLY(VALUES(SUBSTRING(t1.num,1,5)
,CONVERT(INT,SUBSTRING(t1.num,6,3))
,CASE WHEN LEN(t1.num)>10 THEN CONVERT(INT,SUBSTRING(t1.num,10,100)) ELSE CONVERT(INT,SUBSTRING(t1.num,6,3)) END
)) c(prefix,f,t)
LEFT JOIN master.dbo.spt_values AS sv ON sv.type='P' AND sv.number BETWEEN c.f AND c.t
+----+----------------+----------+---------+
| id | num | conntect | decmial |
+----+----------------+----------+---------+
| 1 | 000-1001 | 1001 | 100 |
| 2 | 000-1002 | 1002 | 200 |
| 2 | 000-1002-0003 | 1002 | NULL |
| 2 | 000-1002-0004 | 1002 | NULL |
| 2 | 000-1002-0005 | 1002 | NULL |
| 3 | 000-1006 | 1003 | 300 |
| 6 | 000-1011 | 1006 | 600 |
| 6 | 000-1011-00012 | 1006 | NULL |
| 6 | 000-1011-00013 | 1006 | NULL |
| 6 | 000-1011-00014 | 1006 | NULL |
| 6 | 000-1011-00015 | 1006 | NULL |
| 6 | 000-1011-00016 | 1006 | NULL |
+----+----------------+----------+---------+
SELECT number FROM [master]..spt_values
WHERE TYPE='p'
这个应该就是你说的序号表了吧
SELECT number FROM [master]..spt_values
WHERE TYPE='p'
这个应该就是你说的序号表了吧[/quote]
对,就是这个表