try:select [year],[month],key, (select sum(qty) from tab where [year]=a.[year] and [month]<=a.[month] and key=a.key) as qty from tab a
SELECT year,month,key, QTY=(SELECT SUM(qty) FROM TB WHERE YEAR=A.YEAR AND KEY=A.KEY AND MONTH<=A.MONTH) FROM TB A
--先试一下这样的效率SELECT year,month,key, ( SELECT SUM(qty) FROM tableName WHERE CAST(CAST(year as varchar(4)+'/'+Cast(month as varchar(2)+'/'+'01' as Datetime) <= CAST(CAST(a.year as varchar(4)+'/'+Cast(a.month as varchar(2)+'/'+'01' as Datetime) AND Key=a.Key ) as totalqty FROM TableName a
create table tb([year] smallint,[month] int,[key] int,qty int) insert into tb values(2008,1,2581,100) insert into tb values(2008,2,2581,300) insert into tb values(2008,1,8564,3) insert into tb values(2008,2,8564,4) goselect [year] ,[month] ,[key] , [qty] = (select sum(qty) from tb where [key] = t.[key] and [year] = 2008 and [month] <= t.[month]) from tb t where [year] = 2008drop table tb/* year month key qty ------ ----------- ----------- ----------- 2008 1 2581 100 2008 2 2581 400 2008 1 8564 3 2008 2 8564 7(所影响的行数为 4 行) */
----先保证数据正确哈..declare @t table(yr int,mon int,ky int,qty int) insert into @t select 2008,1,2581,100 UNION ALL SELECT 2008,2,2581,300 UNION ALL SELECT 2008,1,8564,3 UNION ALL SELECT 2008,2,8564,4 SELECT yr,mon,ky, ( SELECT SUM(qty) FROM @t WHERE CAST(CAST(yr as varchar(4))+'/'+Cast(mon as varchar(2))+'/'+'01' as Datetime) <= CAST(CAST(a.yr as varchar(4))+'/'+Cast(a.mon as varchar(2))+'/'+'01' as Datetime) AND ky=a.ky ) as totalqty FROM @t a /* yr mon ky totalqty ----------- ----------- ----------- ----------- 2008 1 2581 100 2008 2 2581 400 2008 1 8564 3 2008 2 8564 7 */
用“可更新的游标”算,速度能快得了么 =+= SELECT year,month,[key] ,qty=(SELECT SUM(qty) FROM TB WHERE [key]=a.[key] AND YEAR=a.YEAR AND MONTH<=a.MONTH) FROM tb a
各位,我回来了。这次周末休息了三天。很抱歉没能及时回复大家的建议。 首先说明,这是单位的商业数据库,表的结构是不能修改的,所以是没法子在表上建立聚集索引的。 因此,这确实是一个非常棘手的问题,我问领导,要是仅仅算某一个KEY的累计,速度会非常快。但领导要求,先试着算出所有的数据,看到底需要多少时间。 我用类似一下的方法计算 SELECT yr,mon,ky, ( SELECT SUM(qty) FROM @t WHERE CAST(CAST(yr as varchar(4))+'/'+Cast(mon as varchar(2))+'/'+'01' as Datetime) <= CAST(CAST(a.yr as varchar(4))+'/'+Cast(a.mon as varchar(2))+'/'+'01' as Datetime) AND ky=a.ky ) as totalqty FROM @t a或者是SELECT year,month,[key] ,qty=(SELECT SUM(qty) FROM TB WHERE [key]=a.[key] AND YEAR=a.YEAR AND MONTH<=a.MONTH) FROM tb a大约是4分钟。大家想到的大都是这个办法。我想到的也是这个方法,但时间太长。 (我的计算还加了一个查询,于是用了8分钟)于是考虑用可更新游标,排序后,遍历一遍,同时计算汇总,然后更新临时表。 结果速度并没有加快。还是4分多。看来得要求进一步地限制条件了。结帖。
(select sum(qty) from tab where [year]=a.[year] and [month]<=a.[month] and key=a.key) as qty
from tab a
QTY=(SELECT SUM(qty) FROM TB WHERE YEAR=A.YEAR AND KEY=A.KEY AND MONTH<=A.MONTH)
FROM TB A
(
SELECT SUM(qty)
FROM tableName
WHERE CAST(CAST(year as varchar(4)+'/'+Cast(month as varchar(2)+'/'+'01' as Datetime)
<=
CAST(CAST(a.year as varchar(4)+'/'+Cast(a.month as varchar(2)+'/'+'01' as Datetime)
AND Key=a.Key
) as totalqty
FROM TableName a
insert into tb values(2008,1,2581,100)
insert into tb values(2008,2,2581,300)
insert into tb values(2008,1,8564,3)
insert into tb values(2008,2,8564,4)
goselect [year] ,[month] ,[key] , [qty] = (select sum(qty) from tb where [key] = t.[key] and [year] = 2008 and [month] <= t.[month]) from tb t where [year] = 2008drop table tb/*
year month key qty
------ ----------- ----------- -----------
2008 1 2581 100
2008 2 2581 400
2008 1 8564 3
2008 2 8564 7(所影响的行数为 4 行)
*/
----先保证数据正确哈..declare @t table(yr int,mon int,ky int,qty int)
insert into @t
select 2008,1,2581,100 UNION ALL SELECT
2008,2,2581,300 UNION ALL SELECT
2008,1,8564,3 UNION ALL SELECT
2008,2,8564,4 SELECT yr,mon,ky,
(
SELECT SUM(qty)
FROM @t
WHERE CAST(CAST(yr as varchar(4))+'/'+Cast(mon as varchar(2))+'/'+'01' as Datetime)
<=
CAST(CAST(a.yr as varchar(4))+'/'+Cast(a.mon as varchar(2))+'/'+'01' as Datetime)
AND ky=a.ky
) as totalqty
FROM @t a
/*
yr mon ky totalqty
----------- ----------- ----------- -----------
2008 1 2581 100
2008 2 2581 400
2008 1 8564 3
2008 2 8564 7
*/
主要是数据过滤花费了时间,如8楼所说,建索引才是根本年月上建非聚集复合索引,然后用以上各位的查询
SELECT year,month,[key]
,qty=(SELECT SUM(qty) FROM TB WHERE [key]=a.[key] AND YEAR=a.YEAR AND MONTH<=a.MONTH)
FROM tb a
首先说明,这是单位的商业数据库,表的结构是不能修改的,所以是没法子在表上建立聚集索引的。
因此,这确实是一个非常棘手的问题,我问领导,要是仅仅算某一个KEY的累计,速度会非常快。但领导要求,先试着算出所有的数据,看到底需要多少时间。
我用类似一下的方法计算
SELECT yr,mon,ky,
(
SELECT SUM(qty)
FROM @t
WHERE CAST(CAST(yr as varchar(4))+'/'+Cast(mon as varchar(2))+'/'+'01' as Datetime)
<=
CAST(CAST(a.yr as varchar(4))+'/'+Cast(a.mon as varchar(2))+'/'+'01' as Datetime)
AND ky=a.ky
) as totalqty
FROM @t a或者是SELECT year,month,[key]
,qty=(SELECT SUM(qty) FROM TB WHERE [key]=a.[key] AND YEAR=a.YEAR AND MONTH<=a.MONTH)
FROM tb a大约是4分钟。大家想到的大都是这个办法。我想到的也是这个方法,但时间太长。
(我的计算还加了一个查询,于是用了8分钟)于是考虑用可更新游标,排序后,遍历一遍,同时计算汇总,然后更新临时表。
结果速度并没有加快。还是4分多。看来得要求进一步地限制条件了。结帖。