数据库中有以下字段,要求是 求 StockId 相同的 ,按时间顺序排序 最近 5天的 fVol的 和。怎么写???中间可能有几天没数据,那样就向前移动,反正总保证统计5天的和DataTime StockId fVol2008-2-21 000800 289928.4375
2008-2-22 000800 503016.1875
2008-2-25 000800 -10095.21582
2008-2-26 000800 -36981.878906
2008-2-27 000800 644.995361
2008-2-28 000800 2198.187988
2008-2-29 000800 5654.983398
2008-3-3 000800 5546.563965
2008-3-4 000800 67387.898438
2008-3-5 000800 7533.382813
2008-3-6 000800 -4209.424805
2008-3-7 000800 23237.363281
2008-3-10 000800 -38967.34375
2008-3-11 000800 2516.839844
2008-3-12 000800 -14635.929688
2008-3-13 000800 11029.654297
2008-3-14 000800 7870.117676
2008-3-17 000800 -21573.396484
2008-3-18 000800 -41693.90625
2008-3-19 000800 53400.054688
2008-3-20 000800 1221.0087892008-2-21 600000 -24493.21875
2008-2-22 600000 550059.6875
2008-2-25 600000 -18295.4375
2008-2-26 600000 -81992.4375
2008-2-27 600000 84372.671875
2008-2-28 600000 -10039.753906
2008-2-29 600000 -715.375
2008-3-3 600000 26328.328125
2008-3-4 600000 22442.785156
2008-3-5 600000 -5300.082031
2008-3-6 600000 136585.125
2008-3-7 600000 61463.832031
2008-3-10 600000 -11534.589844
2008-3-11 600000 -16570.445313
2008-3-12 600000 85550.460938
2008-3-13 600000 14662.78125
2008-3-14 600000 4119.99707
2008-3-17 600000 30416.230469
2008-3-18 600000 -2990.662109
2008-3-20 600000 2153.3935552008-2-21 600123 15333.599609
2008-2-22 600123 50580.011719
2008-2-25 600123 8075.831055
2008-2-26 600123 19945.679688
2008-2-27 600123 48812.039063
2008-2-28 600123 -1895.038086
2008-2-29 600123 1780.611816
2008-3-3 600123 2988.090088
2008-3-4 600123 20207.595703
2008-3-5 600123 7342.390625
2008-3-6 600123 10115.193359
2008-3-7 600123 12906.525391
2008-3-10 600123 903.959106
2008-3-11 600123 2707.079102
2008-3-12 600123 29203.908203
2008-3-13 600123 40491.042969
2008-3-14 600123 14928.75
2008-3-17 600123 101430.015625
2008-3-18 600123 -6271.519531
2008-3-19 600123 412785.5625
2008-3-20 600123 4467.646973
2008-2-22 000800 503016.1875
2008-2-25 000800 -10095.21582
2008-2-26 000800 -36981.878906
2008-2-27 000800 644.995361
2008-2-28 000800 2198.187988
2008-2-29 000800 5654.983398
2008-3-3 000800 5546.563965
2008-3-4 000800 67387.898438
2008-3-5 000800 7533.382813
2008-3-6 000800 -4209.424805
2008-3-7 000800 23237.363281
2008-3-10 000800 -38967.34375
2008-3-11 000800 2516.839844
2008-3-12 000800 -14635.929688
2008-3-13 000800 11029.654297
2008-3-14 000800 7870.117676
2008-3-17 000800 -21573.396484
2008-3-18 000800 -41693.90625
2008-3-19 000800 53400.054688
2008-3-20 000800 1221.0087892008-2-21 600000 -24493.21875
2008-2-22 600000 550059.6875
2008-2-25 600000 -18295.4375
2008-2-26 600000 -81992.4375
2008-2-27 600000 84372.671875
2008-2-28 600000 -10039.753906
2008-2-29 600000 -715.375
2008-3-3 600000 26328.328125
2008-3-4 600000 22442.785156
2008-3-5 600000 -5300.082031
2008-3-6 600000 136585.125
2008-3-7 600000 61463.832031
2008-3-10 600000 -11534.589844
2008-3-11 600000 -16570.445313
2008-3-12 600000 85550.460938
2008-3-13 600000 14662.78125
2008-3-14 600000 4119.99707
2008-3-17 600000 30416.230469
2008-3-18 600000 -2990.662109
2008-3-20 600000 2153.3935552008-2-21 600123 15333.599609
2008-2-22 600123 50580.011719
2008-2-25 600123 8075.831055
2008-2-26 600123 19945.679688
2008-2-27 600123 48812.039063
2008-2-28 600123 -1895.038086
2008-2-29 600123 1780.611816
2008-3-3 600123 2988.090088
2008-3-4 600123 20207.595703
2008-3-5 600123 7342.390625
2008-3-6 600123 10115.193359
2008-3-7 600123 12906.525391
2008-3-10 600123 903.959106
2008-3-11 600123 2707.079102
2008-3-12 600123 29203.908203
2008-3-13 600123 40491.042969
2008-3-14 600123 14928.75
2008-3-17 600123 101430.015625
2008-3-18 600123 -6271.519531
2008-3-19 600123 412785.5625
2008-3-20 600123 4467.646973
from tb a
where datatime in (select top 5 datatime from tb where stockid=a.stockid order by datatime desc)
group by stockid
insert into @tb select '2008-01-01'
insert into @tb select '2008-01-02'
insert into @tb select '2008-01-05'
insert into @tb select '2008-01-06'
insert into @tb select '2008-01-07'
insert into @tb select '2008-01-08'
insert into @tb select '2008-01-12'
insert into @tb select '2008-01-13'
select * from @tb
where dt in(
select top 5 dt from (select distinct dt from @tb)tp
where dt<'2008-01-15'
order by dt desc)2008-01-06 00:00:00.000
2008-01-07 00:00:00.000
2008-01-08 00:00:00.000
2008-01-12 00:00:00.000
2008-01-13 00:00:00.000
select
StockId ,sum( fVol) as fVol
from tablename
where dt in(
select top 5 dt from (select distinct dt from tablename)tp
where dt<getdate()
order by dt desc)
group by StockId估计
StockId ,sum( fVol) as fVol
from tablename a
where dt in(
select top 5 dt from (select distinct dt from tablename where a.stockid = stockid)tp
where dt<getdate()
order by dt desc)
group by StockId
TOP 5 StockId, SUM(fVol) AS fVol
FROM TABLE1 AS A
INNER JOIN (SELECT TOP 5 datatime,stockid FROM TABLE1 ORDER BY datatime DESC) AS B ON A.stockid = B.stockid
GROUP BY StockId
From Table1
Where DatePart(dd,[Datetime])>(DatePart(dd,getdate())-5)
Group By StockId
From Table1
Where DatePart(dd,[Datetime])>(DatePart(dd,getdate())-5)
Group By stockid,[Datetime]
Order By Stockid这个没事了
--按楼主题意,以下两个写法结果一样.--取所有时间的.
select t.* from tb t
where DataTime in
(select top 5 DataTime from tb where StockId = t.StockId order by DataTime desc)--取小于当天的.
select t.* from tb t
where DataTime < getdate() and DataTime in
(select top 5 DataTime from tb where StockId = t.StockId DataTime < getdate() order by DataTime desc)
declare @tb table (dt datetime,stockid char(6),fvol numeric(20,5))
insert into @tb select '2008-2-21','000800',289928.4375
insert into @tb select '2008-2-22','000800',503016.1875
insert into @tb select '2008-2-25','000800',-10095.21582
insert into @tb select '2008-2-26','000800',-36981.878906
insert into @tb select '2008-2-27','000800',644.995361
insert into @tb select '2008-2-28','000800',2198.187988
insert into @tb select '2008-2-29','000800',5654.983398
insert into @tb select '2008-3-3','000800', 5546.563965 insert into @tb select '2008-2-21','600000',-24493.21875
insert into @tb select '2008-2-22','600000',550059.6875
insert into @tb select '2008-2-25','600000',-18295.4375
insert into @tb select '2008-2-26','600000', -81992.4375
insert into @tb select '2008-2-27','600000',84372.671875
insert into @tb select '2008-2-28','600000',-10039.753906
insert into @tb select '2008-2-29','600000',-715.375
insert into @tb select '2008-3-3','600000',26328.328125 select StockId ,sum( fVol) as fVol from @tb
where dt in( select top 5 dt from (select distinct dt from @tb )tp where dt <getdate() order by dt desc)
group by StockId
insert into @tb select '2008-2-21','000800',289928.4375
insert into @tb select '2008-2-22','000800',503016.1875
insert into @tb select '2008-2-25','000800',-10095.21582
insert into @tb select '2008-2-26','000800',-36981.878906
insert into @tb select '2008-2-27','000800',644.995361
insert into @tb select '2008-2-28','000800',2198.187988
insert into @tb select '2008-2-29','000800',5654.983398
insert into @tb select '2008-3-3','000800', 5546.563965 insert into @tb select '2008-2-21','600000',-24493.21875
insert into @tb select '2008-2-22','600000',550059.6875
insert into @tb select '2008-2-25','600000',-18295.4375
insert into @tb select '2008-2-26','600000', -81992.4375
insert into @tb select '2008-2-27','600000',84372.671875
insert into @tb select '2008-2-28','600000',-10039.753906
insert into @tb select '2008-2-29','600000',-715.375
insert into @tb select '2008-3-3','600000',26328.328125
select StockID,sum(fVol)
from @tb a
where
exists(select * from @tb where StockID=a.StockID and dt>=a.dt group by StockID having count(1)<=5 )
group by StockID
--还要取和?看漏了.--取所有时间的.
select StockId, fVol = sum(fVol) from tb t
where DataTime in
(select top 5 DataTime from tb where StockId = t.StockId order by DataTime desc)
group by StockId--取小于当天的.
select StockId, fVol = sum(fVol) from tb t
where DataTime < getdate() and DataTime in
(select top 5 DataTime from tb where StockId = t.StockId DataTime < getdate() order by DataTime desc)
group by StockId
insert into @tb select '2008-3-11','000800',289928.4375
insert into @tb select '2008-2-22','000800',503016.1875
insert into @tb select '2008-2-25','000800',-10095.21582
insert into @tb select '2008-2-26','000800',-36981.878906
insert into @tb select '2008-2-27','000800',644.995361
insert into @tb select '2008-2-28','000800',2198.187988
insert into @tb select '2008-2-29','000800',5654.983398
insert into @tb select '2008-3-3','000800', 5546.563965 insert into @tb select '2008-3-2','600000',-24493.21875
insert into @tb select '2008-2-22','600000',550059.6875
insert into @tb select '2008-2-25','600000',-18295.4375
insert into @tb select '2008-2-26','600000', -81992.4375
insert into @tb select '2008-2-27','600000',84372.671875
insert into @tb select '2008-2-28','600000',-10039.753906
insert into @tb select '2008-2-29','600000',-715.375
insert into @tb select '2008-3-3','600000',26328.328125 select * From
(
select top 10 stockid, (dt)
from
@tb
group by stockid ,dt
order by max(dt) desc
)c
order by stockid ,dt
------- ------------------------------------------------------ ----------------------
000800 2008-02-27 00:00:00.000 644.99536
000800 2008-02-28 00:00:00.000 2198.18799
000800 2008-02-29 00:00:00.000 5654.98340
000800 2008-03-03 00:00:00.000 5546.56397
000800 2008-03-11 00:00:00.000 289928.43750
600000 2008-02-27 00:00:00.000 84372.67188
600000 2008-02-28 00:00:00.000 -10039.75391
600000 2008-02-29 00:00:00.000 -715.37500
600000 2008-03-02 00:00:00.000 -24493.21875
600000 2008-03-03 00:00:00.000 26328.32813(所影响的行数为 10 行)
select stockid,
sumfval=(select sum(fvol) from tt where stockid=a.stockid
and datetime in (select distinct top 5 datetime
from tt where stockid=a.stockid order by datetime desc))
(select distinct stockid from tt) a
--上面录入出错,少了from 现更正
--设表名为tt
select stockid,
sumfval=(select sum(fvol) from tt where stockid=a.stockid
and datetime in (select distinct top 5 datetime
from tt where stockid=a.stockid order by datetime desc))
from
(select distinct stockid from tt) a
select stockid,sum(fvol) from tab a
where datatime in (select top 5 datatime from tab b
where a.stockid=b.stockid and a.datetime>=b.datetime
group by b.datatime
order by datetime desc)
group by stockid
FROM tablename
WHERE DataTime IN
(
SELECT TOP 5 DataTime
FROM tablename
)
GROUP BY StockId不知道楼主是不是这个意思啊?