create table table1 ( id char(10), data int, ttime char(10) ) insert into table1 select '1',10,'2007-07-17' union all select '2',14,'2007-07-18' union all select '3',1,'2007-07-17' union all select '4',3,'2007-07-18' select max(ttime),sum(data) from table1 group by ttime
select count(*) from table where DateField<GETDATE()
declare @t table(date datetime) insert @t select '2007-07-18 11:22:56' union all select '2007-07-18 12:22:56' union all select '2007-07-18 13:22:56' union all select '2007-07-18 14:22:56' union all select '2007-07-19 11:22:56' union all select '2007-07-19 12:22:56' union all select '2007-07-19 13:22:56' union all select '2007-07-19 14:22:56' union all select '2007-07-19 15:22:56' union all select '2007-07-20 11:22:56' union all select '2007-07-20 12:22:56' select date = convert(varchar(10), a.date, 120), total = max(a.total) from (select date, total = (select count(1) from @t where date <= t.date) from @t t) a group by convert(varchar(10), a.date, 120)/* date total ---------- ----------- 2007-07-18 4 2007-07-19 9 2007-07-20 11(所影响的行数为 3 行) */
----tryselect convert(char(10),时间,120), sum(值)+isnull((select sum(值) from 表 b where convert(char(10),b.时间,120)<convert(char(10),a.时间,120)),0) from 表 a
從裏層往外層講吧首先,我們要以天為單位,得到每天以及之前所有日期內記錄項總和,但由於日期字段是精確到秒的,所以先對所有時間進行累計,即: select date, total = (select count(1) from @t where date <= t.date) from @t t 得到的結果集為 date total -------------------------- ----------- 2007-07-18 11:22:56.000 1 2007-07-18 12:22:56.000 2 2007-07-18 13:22:56.000 3 2007-07-18 14:22:56.000 4 2007-07-19 11:22:56.000 5 2007-07-19 12:22:56.000 6 2007-07-19 13:22:56.000 7 2007-07-19 14:22:56.000 8 2007-07-19 15:22:56.000 9 2007-07-20 11:22:56.000 10 2007-07-20 12:22:56.000 11total列是對每個時間之前的記錄數累計求和得到的得到這個結果集之後只需要從中找出每天的最後一個時間及其對應的累計值就可以了 將這個結果集視為表a,那麼就是 select date = convert(varchar(10), a.date, 120), total = max(a.total) from a group by convert(varchar(10), a.date, 120)--------------另外,count(1)和count(*)是一樣的 希望這樣講能讓你明白:)當然,這祇是我的思路,不排除有更好的算法
declare @t table(date datetime) insert @t select '2007-07-18 11:22:56' union all select '2007-07-18 12:22:56' union all select '2007-07-18 13:22:56' union all select '2007-07-18 14:22:56' union all select '2007-07-19 11:22:56' union all select '2007-07-19 12:22:56' union all select '2007-07-19 13:22:56' union all select '2007-07-19 14:22:56' union all select '2007-07-19 15:22:56' union all select '2007-07-20 11:22:56' union all select '2007-07-20 12:22:56' select [天]=convert(varchar(10),date,120),[天记录]=count(*), [总和]=(select count(1) from @t where convert(varchar(10),date,120)!>convert(varchar(10),t.date,120)) from @t t group by convert(varchar(10),date,120)(所影响的行数为 11 行)天 天记录 总和 ---------- ----------- ----------- 2007-07-18 4 4 2007-07-19 5 9 2007-07-20 2 11(所影响的行数为 3 行)
(
id char(10),
data int,
ttime char(10)
)
insert into table1
select '1',10,'2007-07-17'
union all
select '2',14,'2007-07-18'
union all
select '3',1,'2007-07-17'
union all
select '4',3,'2007-07-18'
select max(ttime),sum(data) from table1 group by ttime
insert @t select '2007-07-18 11:22:56'
union all select '2007-07-18 12:22:56'
union all select '2007-07-18 13:22:56'
union all select '2007-07-18 14:22:56'
union all select '2007-07-19 11:22:56'
union all select '2007-07-19 12:22:56'
union all select '2007-07-19 13:22:56'
union all select '2007-07-19 14:22:56'
union all select '2007-07-19 15:22:56'
union all select '2007-07-20 11:22:56'
union all select '2007-07-20 12:22:56'
select date = convert(varchar(10), a.date, 120), total = max(a.total)
from
(select date,
total = (select count(1) from @t where date <= t.date)
from @t t) a
group by convert(varchar(10), a.date, 120)/*
date total
---------- -----------
2007-07-18 4
2007-07-19 9
2007-07-20 11(所影响的行数为 3 行)
*/
sum(值)+isnull((select sum(值) from 表 b where convert(char(10),b.时间,120)<convert(char(10),a.时间,120)),0)
from 表 a
能否给偶们这些菜鸟们详细说明一下思路呢?
另外COUNT(1) 与COUNT(*)有什么不一样呢
select date, total = (select count(1) from @t where date <= t.date)
from @t t
得到的結果集為
date total
-------------------------- -----------
2007-07-18 11:22:56.000 1
2007-07-18 12:22:56.000 2
2007-07-18 13:22:56.000 3
2007-07-18 14:22:56.000 4
2007-07-19 11:22:56.000 5
2007-07-19 12:22:56.000 6
2007-07-19 13:22:56.000 7
2007-07-19 14:22:56.000 8
2007-07-19 15:22:56.000 9
2007-07-20 11:22:56.000 10
2007-07-20 12:22:56.000 11total列是對每個時間之前的記錄數累計求和得到的得到這個結果集之後只需要從中找出每天的最後一個時間及其對應的累計值就可以了
將這個結果集視為表a,那麼就是
select date = convert(varchar(10), a.date, 120), total = max(a.total)
from a
group by convert(varchar(10), a.date, 120)--------------另外,count(1)和count(*)是一樣的
希望這樣講能讓你明白:)當然,這祇是我的思路,不排除有更好的算法
insert @t select '2007-07-18 11:22:56'
union all select '2007-07-18 12:22:56'
union all select '2007-07-18 13:22:56'
union all select '2007-07-18 14:22:56'
union all select '2007-07-19 11:22:56'
union all select '2007-07-19 12:22:56'
union all select '2007-07-19 13:22:56'
union all select '2007-07-19 14:22:56'
union all select '2007-07-19 15:22:56'
union all select '2007-07-20 11:22:56'
union all select '2007-07-20 12:22:56'
select [天]=convert(varchar(10),date,120),[天记录]=count(*),
[总和]=(select count(1) from @t where convert(varchar(10),date,120)!>convert(varchar(10),t.date,120))
from @t t
group by convert(varchar(10),date,120)(所影响的行数为 11 行)天 天记录 总和
---------- ----------- -----------
2007-07-18 4 4
2007-07-19 5 9
2007-07-20 2 11(所影响的行数为 3 行)
解释的很清楚 ^_^