首先,获得最近7天的记录,可以不用循环 select daytime = convert(datetime,convert(char(8),time_go,112)), points=sum(points) from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) group by convert(datetime,convert(char(8),time_go,112))
select daytime = convert(datetime,convert(char(8),time_go,112)), points= from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) union select null,sum(points) from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) group by convert(datetime,convert(char(8),time_go,112)) 楼主是这个意思,在最后一条记录后再加一行,是吧
更正楼上笔误: select daytime = convert(datetime,convert(char(8),time_go,112)), points= from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) union select null,sum(points) from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) ---group by convert(datetime,convert(char(8),time_go,112)) ---去掉这一行
再更正楼上笔误: select daytime = convert(datetime,convert(char(8),time_go,112)), points=sum(points) ---这里补齐 from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) group by convert(datetime,convert(char(8),time_go,112)) ---加上这一行 union select null,sum(points) from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) ---group by convert(datetime,convert(char(8),time_go,112)) ---去掉这一行
TO hmily1688(刘胜涛--进军WAP) & To tx1icenhe(冒牌马可 V0.1) : 谢谢你们的帮助,但是还没有得到我的要求,我想返回7跳记录,无论 points 是否为空,daytime 字段都要返回一个那一天的时间,这样一定要用到循环,但是不太会用,请再帮忙,谢谢。
select dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112))) as daytime,b.points from ( select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 ) as t left join ( select daytime = convert(datetime,convert(char(8),time_go,112)), points=sum(points) from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) group by convert(datetime,convert(char(8),time_go,112)) ) as b on dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112)))=b.daytime
排下序:select dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112))) as daytime,b.points from ( select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 ) as t left join ( select daytime = convert(datetime,convert(char(8),time_go,112)), points=sum(points) from sheet_stander where user_id = @u_id and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112)) group by convert(datetime,convert(char(8),time_go,112)) ) as b on dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112)))=b.daytime order by dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112)))
select daytime = convert(datetime,convert(char(8),time_go,112)),
points=sum(points)
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
group by convert(datetime,convert(char(8),time_go,112))
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
union
select null,sum(points)
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
group by convert(datetime,convert(char(8),time_go,112))
楼主是这个意思,在最后一条记录后再加一行,是吧
select daytime = convert(datetime,convert(char(8),time_go,112)), points=
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
union
select null,sum(points)
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
---group by convert(datetime,convert(char(8),time_go,112)) ---去掉这一行
select daytime = convert(datetime,convert(char(8),time_go,112)), points=sum(points) ---这里补齐
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
group by convert(datetime,convert(char(8),time_go,112)) ---加上这一行
union
select null,sum(points)
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
---group by convert(datetime,convert(char(8),time_go,112)) ---去掉这一行
daytime points
------------------------------------------------------ -----------
NULL 2700
2004-07-30 00:00:00.000 2400
2004-07-31 00:00:00.000 100
2004-08-01 00:00:00.000 100
2004-12-30 00:00:00.000 100(所影响的行数为 5 行)我想返回7跳记录,无论 points 是否为空,daytime 字段不能为空,请大家再帮帮忙
谢谢你们的帮助,但是还没有得到我的要求,我想返回7跳记录,无论 points 是否为空,daytime 字段都要返回一个那一天的时间,这样一定要用到循环,但是不太会用,请再帮忙,谢谢。
select 0 as a
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) as t left join (
select daytime = convert(datetime,convert(char(8),time_go,112)),
points=sum(points)
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
group by convert(datetime,convert(char(8),time_go,112))
) as b
on dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112)))=b.daytime
select 0 as a
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
) as t left join (
select daytime = convert(datetime,convert(char(8),time_go,112)),
points=sum(points)
from sheet_stander
where user_id = @u_id
and time_go >=convert(datetime,convert(char(8),dateadd(day,-7,getdate()),112))
group by convert(datetime,convert(char(8),time_go,112))
) as b
on dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112)))=b.daytime
order by dateadd(day,-t.a,convert(datetime,convert(char(8),getdate(),112)))