——————请问，这样的视图（或临时表）能用SQL建立吗？————————

[dongguacha]

已有表T1（A1，数值1，数值2，....数值5），想在T1的基础上建立这样一个视图（或临时表）：

V1（B，数值），每五条记录的B都是由B1=A1，B2=A1+20，B3=A1+30....B5=A1+50如此类推得出的，而V1的“数值”则是T1中A1所在记录的相应字段“数值1”、“数值2”....“数值5”

示例：

T1 (100，12，34，56，78，90)

生成V1 100，12
110，34
120，56
130，78
140，90

用SQL语句能否实现这样的需求呢，请各路高手多多指点！谢谢！

[$扫地僧$]

create table A
(
A1 int,
num1 int,
num2 int,
num3 int,
num4 int,
num5 int
)
insert A select 100,12,34,56,78,90

create view V1
as
select a1,num1 from A
union all
select a1+10,num2 from A
union all
select a1+20,num3 from A
union all
select a1+30,num4 from A
union all
select a1+40,num5 from A

select * from V1

[zonelive]

create function f_tb(@col1 int)
returns table @a(cl1 int,cl2 int)
as
begin
insert @a
select cl1,cl2 from A
union all
select cl1+10,cl3 from A
union all
select cl1+20,cl4 from A
union all
select cl1+30,cl5 from A
union all
select cl1+40,cl6 from A
return @a
end

[wgsasd311]

create table A
(
A1 int,
num1 int,
num2 int,
num3 int,
num4 int,
num5 int
)
insert A select 100,12,34,56,78,90
union all select 300,3,4,6,7,9
union all select 320,13,14,16,17,19
union all select 400,23,24,26,27,29
union all select 500,3,4,6,7,9
go
create view v1 as
select col1=a1+col,
col2=(case col when 0 then num1 when 10 then num2 when 20 then num3 when 30 then num4 when 40 then num5 end)
from
(select 0 as col union all
select 10 as col union all
select 20 as col union all
select 30 as col union all
select 40 as col )b cross join A
go
select * from v1
drop table A
DROP VIEW V1

[dongguacha]

UP

[ReViSion]

--借用zonelive(peter) 的改进
create table A
(
A1 int,
num1 int,
num2 int,
num3 int,
num4 int,
num5 int
)
insert A select 100,12,34,56,78,90
insert A select 200,12,34,56,78,90

go
create view V1
as
select top 100 percent a1,num1 from
(select a1,num1,a1 b from A
union all
select a1+10,num2,a1 b from A
union all
select a1+20,num3,a1 b from A
union all
select a1+30,num4,a1 b from A
union all
select a1+40,num5,a1 b from A)a
order by b

select * from V1

[dongguacha]

ding

[yinwun]

create table #A(A1 int,num1 int,num2 int,num3 int,num4 int,num5 int)
insert #A select 100,12,34,56,78,90

select * from #A

create table #temp(A1 int,Num int)

insert into #temp
select A1,num1 from #A
union all
select A1+10,num2 from #A
union all
select A1+20,num3 from #A
union all
select A1+30,num4 from #A
union all
select A1+40,num5 from #A

[dongguacha]

顺带再问一句，时间字段的运算用SQL语句怎样写？

[ReViSion]

--那要看你想运算什么啦。
--主要有下面几个运算
DATEADD
DATEDIFF
DATENAME
DATEPART
DAY
GETDATE
YEAR
MONTH
GETUTCDATE
具体意思看联机帮助吧

[dongguacha]

谢谢各位！

[storm2003]

create view V1
as
select top 100 percent a1,num1 from
(select a1,num1,a1 b from A
union all
select a1+10,num2,a1 b from A
union all
select a1+20,num3,a1 b from A
union all
select a1+30,num4,a1 b from A
union all
select a1+40,num5,a1 b from A)a
order by b