比如说——with t1 as
(
select ..... as origin
),
t2 as
(
--这里怎么写?
)
使得t1是类似1,3,5,-2,-4,2
t2是类似null, 9, 6, -1, -2, null就是每一行t1的连续三行的和?问题比较古怪……期待解答~
(
select ..... as origin
),
t2 as
(
--这里怎么写?
)
使得t1是类似1,3,5,-2,-4,2
t2是类似null, 9, 6, -1, -2, null就是每一行t1的连续三行的和?问题比较古怪……期待解答~
1,3,5,-2,-4,2算法说明:
表t2的第i行是表t1的第i-1, i, i+1三行的和最终效果:
select * from t1
1
3
5
-2
-4
2select * from t2
null
9
6
-1
-2
null
from
(
select 1 as origin
union all select 3
union all select 5
union all select -2
union all select -4
union all select 2
) tselect col1,col2=case when col3=3 then col2 else null end
from
(
select a.rn,a.origin as col1,sum(b.origin) as col2,count(1) as col3
from
(select *,rn=row_number() over(order by getdate()) from #) a
left join
(select *,rn=row_number() over(order by getdate()) from #) b
on a.rn=b.rn-1 or a.rn=b.rn or a.rn=b.rn+1
group by a.origin,a.rn
) t/**
col1 col2
----------- -----------
1 NULL
3 9
5 6
-2 -1
-4 -4
2 NULL(6 行受影响)
**/
不知道楼主说的是不是这样?
-- Author : htl258(Tony)
-- Date : 2010-05-08 23:44:50
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [t1]
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([id] [int],[num] [int])
INSERT INTO [t1]
SELECT '1','1' UNION ALL
SELECT '2','3' UNION ALL
SELECT '3','5' UNION ALL
SELECT '4','-2' UNION ALL
SELECT '5','-4' UNION ALL
SELECT '6','2'--SELECT * FROM [t1]-->SQL查询如下:
;with t as
(
select null id,null num
union all
select * from t1
union all
select top(select count(1)%3+2 from t1) null,null from t1
)
,t11 as
(
select rn=row_number()over(order by getdate()),*
from t
)
,t2 as
(
select rn=row_number()over(order by getdate())+1 from t1
)
,t3 as
(
select rn=rn-1,num2=(select num from t11 where rn=t2.rn-1)
+(select num from t11 where rn=t2.rn)
+(select num from t11 where rn=t2.rn+1)
from t2
)
select * from t3
/*
rn num2
-------------------- -----------
1 NULL
2 9
3 6
4 -1
5 -4
6 NULL(6 行受影响)
*/
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([num] [int])
INSERT INTO [t1]
SELECT '1' UNION ALL
SELECT '3' UNION ALL
SELECT '5' UNION ALL
SELECT '-2' UNION ALL
SELECT '-4' UNION ALL
SELECT '2'-->SQL查询如下:
;with t as
(
select rn=row_number()over(order by getdate()),* from t1
)
,t1 as
(
select rn=rn
,num2=(select num from t a where rn=t.rn-1)
+(select num from t a where rn=t.rn)
+(select num from t a where rn=t.rn+1)
from t
)
select * from t1
/*
rn num2
-------------------- -----------
1 NULL
2 9
3 6
4 -1
5 -4
6 NULL(6 行受影响)
*/
简化一下