代码之家 › 专栏 › 技术社区 › geographika

使用SQL重复记录序列

stored-procedures tsql sql-server sql

1

geographika · 技术社区 · 16 年前

这可以使用代码轻松完成,但我想知道是否可以使用SQL Server(2008)在数据库级别完成。

CROP_ID   YEAR_   PRODUCTION   
1         1       0  
1         2       300  
1         3       500  
2         1       100
2         2       700

n

CROP_ID  YEAR_  PRODUCTION  
1        1      0  
1        2      300  
1        3      500  
1        4      0  
1        5      300
1        6      500
etc.

我不确定最好的方法,我想我需要一个SP,传入一个年份变量,并使用循环构造?然而,我无法理解确切的语法。感谢您的帮助。

select top 20 b.CROP_ID,  
YEAR_ = n.num * (select count() from MyBaseTable where CROP_ID = 3) + b.YEAR,  
b.PRODUCTION from MyBaseTable b, dbo.fnMakeNRows(20) n  
where CROP_ID = 3

4 回复 | 直到 16 年前

1

Brett 16 年前

您可以在标准SQL中执行此操作,而无需创建存储过程或使用临时表。下面的例子将持续12年。您可以将其延长到任何年限:

insert into CropYield
(CropID, Year_, Production)
Select 1, a.a + (10 * b.a), 
    case (a.a + (10 * b.a)) % 3
        when 0 then 500
        when 1 then 0
        when 2 then 300
    end
from (Select 0 as a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) as a
cross join (Select 0 as a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) as b
where a.a + (10 * b.a) between 1 and 12

2

D'Arcy Rittich 16 年前

create function fnMakeNRows (@num as integer)
returns @result table (num integer not null) as
begin
 if @num is null or @num = 0
 begin
  return
 end
 declare @n as integer
 set @n = 0
 while @n < @num
 begin
  insert into @result values (@n)
  set @n = @n + 1
 end
 return
end
go

select
 CROP_ID = 1,
 YEAR_ = num,
 PRODUCTION = case num % 3 when 0 then 0 when 1 then 300 else 500 end
from dbo.fnMakeNRows(100000)

select
 b.CROP_ID,
 YEAR_ = n.num * (select count(*) from base_table) + b.YEAR_,
 b.PRODUCTION
from base_table b, dbo.fnMakeNRows(20) n

这(希望)展示了通用fnMakeNRows函数的实用性。

3

1

mjv 16 年前

在不需要存储过程的情况下生成此类数据的一个常见技巧是使用 常数表 因为这样的表可以是通用的,所以可以根据使用情况,使用1到100之间甚至1到1000之间的所有整数来创建它。

CREATE TABLE tblConstNums
( I INT )
INSERT INTO tblConstNums VALUES (1) 
INSERT INTO tblConstNums VALUES (2) 
INSERT INTO tblConstNums VALUES (3) 
INSERT INTO tblConstNums VALUES (4) 
INSERT INTO tblConstNums VALUES (5)
-- ...
INSERT INTO tblConstNums VALUES (1000)

SELECT CROP_ID,  YEAR_ * I,   PRODUCTION 
FROM myCropTable T
JOIN tblConstNums C on 1=1
WHERE I in (1, 2, 3)
order by CROP_ID,  YEAR_ * I,   PRODUCTION

请注意,常数表可能包括几个常用情况的列。例如,尽管其中许多可以表示为基本0到n序列中的数字的数学表达式,但一个列可以只有偶数,另一个列有奇数,另一列有5的倍数等。此外,如果它足够小,常数表上就不需要索引,但这些索引在更大的上可能会变得有用。

4

1

manji 16 年前

WITH tn (n) as
(
    SELECT 0
  UNION ALL
    SELECT n+1
    FROM tn
  WHERE tn.n < 10
)
SELECT DISTINCT t.CROP_ID, t.YEAR_ + (3*tn.n), t.PRODUCTION
FROM table t, tn 

/*    
    WHERE tn.n < 10 ==> you will get 1 -> (10*3) + 3 = 33

*/