当前位置: 代码迷 >> Sql Server >> 用个字段的差来填充行,该怎么处理
  详细解决方案

用个字段的差来填充行,该怎么处理

热度:90   发布时间:2016-04-27 13:00:54.0
用个字段的差来填充行
create table jied(no char(10),name char(10),dat int)

insert into jied
select 'p1','one',1 union
select 'p2','two',8 union
select 'p3','three',20  

--需求结果
p1 one 1
p1 one 2
p1 one 3
p1 one 4
p1 one 5 
p1 one 6
p1 one 7
p2 two 8
p2 two 9
p2 two 10
p2 two 11
p2 two 12
p2 two 13
p2 two 14
p2 two 15
p2 two 16
p2 two 17
p2 two 18
p2 two 19
p3 three 20


------解决方案--------------------
MASTER..SPT_VALUES表构造数据。
------解决方案--------------------
SQL code
SELECT no,A.name,numberFROM jied AS A, MASTER..SPT_VALUES AS BWHERE B.type = 'P' AND DAT <= number AND number < (SELECT CASE WHEN MIN(DAT) IS NULL THEN (SELECT MAX(DAT) + 1 FROM jied)                                                               ELSE MIN(DAT) END FROM jied  AS C WHERE A.dat < C.dat)no    name    numberp1            one           1p1            one           2p1            one           3p1            one           4p1            one           5p1            one           6p1            one           7p2            two           8p2            two           9p2            two           10p2            two           11p2            two           12p2            two           13p2            two           14p2            two           15p2            two           16p2            two           17p2            two           18p2            two           19p3            three         20
------解决方案--------------------
CREATE table xl (
id int null
) ON [PRIMARY]

insert into xl
select number AS N
from master..spt_values
where type='p' and number between 1 and 100



drop table jied

create table jied(no char(10),name char(10),dat int,dat1 int)

insert into jied
select 'p1','one',1,8 union
select 'p2','two',8,20 union
select 'p3','three',20,21

select no,name,id from (
select no,name,dat,dat1 from jied )a
left join
xl b on b.id>=dat and b.id<dat1

我这有个笨办法
  相关解决方案