表
F_DateTime F_Money
2009-07-01 04:00:00 100
2009-07-01 06:01:00 100
2009-07-01 07:10:00 100
2009-07-01 08:20:22 100
2009-07-02 01:20:30 100
2009-07-02 05:23:30 100
2009-07-02 07:24:30 100
统计 每天的 06:00:00 到下一天的 05:59:59 的数据 算一天的数据F_DateTime between '2009-06-30 06:00:00' and '2009-07-03 05:59:59'
结果像这样
F_Date F_Money
2009-06-30 100
2009-07-01 500
2009-07-02 100
F_DateTime F_Money
2009-07-01 04:00:00 100
2009-07-01 06:01:00 100
2009-07-01 07:10:00 100
2009-07-01 08:20:22 100
2009-07-02 01:20:30 100
2009-07-02 05:23:30 100
2009-07-02 07:24:30 100
统计 每天的 06:00:00 到下一天的 05:59:59 的数据 算一天的数据F_DateTime between '2009-06-30 06:00:00' and '2009-07-03 05:59:59'
结果像这样
F_Date F_Money
2009-06-30 100
2009-07-01 500
2009-07-02 100
insert into tb values('2009-07-01 04:00:00' , 100 )
insert into tb values('2009-07-01 06:01:00' , 100 )
insert into tb values('2009-07-01 07:10:00' , 100 )
insert into tb values('2009-07-01 08:20:22' , 100 )
insert into tb values('2009-07-02 01:20:30' , 100 )
insert into tb values('2009-07-02 05:23:30' , 100 )
insert into tb values('2009-07-02 07:24:30' , 100 )
goselect F_Date , sum(F_Money ) F_Money from
(
select case when convert(varchar(8),F_DateTime,114) < '06:00:00' then convert(varchar(10),F_DateTime-1,120) else convert(varchar(10),F_DateTime,120) end F_Date , F_Money from tb
) t
group by F_Datedrop table tb/*
F_Date F_Money
---------- -----------
2009-06-30 100
2009-07-01 500
2009-07-02 100(所影响的行数为 3 行)
*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([F_DateTime] datetime,[F_Money] int)
insert [TB]
select '2009-07-01 04:00:00',100 union all
select '2009-07-01 06:01:00',100 union all
select '2009-07-01 07:10:00',100 union all
select '2009-07-01 08:20:22',100 union all
select '2009-07-02 01:20:30',100 union all
select '2009-07-02 05:23:30',100 union all
select '2009-07-02 07:24:30',100
select F_DateTime=convert(varchar(10),dateadd(hh,-6,F_DateTime),120),
F_Money=sum(F_Money)
from TB
group by convert(varchar(10),dateadd(hh,-6,F_DateTime),120)/*
F_DateTime F_Money
---------- -----------
2009-06-30 100
2009-07-01 500
2009-07-02 100(所影响的行数为 3 行)*/
drop table TB
--> 测试数据:@table
declare @table table([F_DateTime] datetime,[F_Money] int)
insert @table
select '2009-07-01 04:00:00',100 union all
select '2009-07-01 06:01:00',100 union all
select '2009-07-01 07:10:00',100 union all
select '2009-07-01 08:20:22',100 union all
select '2009-07-02 01:20:30',100 union all
select '2009-07-02 05:23:30',100 union all
select '2009-07-02 07:24:30',100select case when convert(varchar(8),F_DateTime,108) >= '06:00:00'
then convert(varchar(10),F_DateTime,120) else
convert(varchar(10),dateadd(day,-1,F_DateTime),120) end as F_DateTime,
sum(F_Money) as F_Money
from @table
group by case when convert(varchar(8),F_DateTime,108) >= '06:00:00'
then convert(varchar(10),F_DateTime,120) else
convert(varchar(10),dateadd(day,-1,F_DateTime),120) end
--结果
---------------------------
2009-06-30 100
2009-07-01 500
2009-07-02 100
insert into tb values('2009-07-01 04:00:00' , 100 )
insert into tb values('2009-07-01 06:01:00' , 100 )
insert into tb values('2009-07-01 07:10:00' , 100 )
insert into tb values('2009-07-01 08:20:22' , 100 )
insert into tb values('2009-07-02 01:20:30' , 100 )
insert into tb values('2009-07-02 05:23:30' , 100 )
insert into tb values('2009-07-02 07:24:30' , 100 )
go
--方法一
select convert(varchar(10),dateadd(hh,-6 , F_DateTime),120) f_date , sum(F_Money) F_Money from tb group by convert(varchar(10),dateadd(hh,-6 , F_DateTime),120)
/*
F_Date F_Money
---------- -----------
2009-06-30 100
2009-07-01 500
2009-07-02 100(所影响的行数为 3 行)
*/
--方法二
select F_Date , sum(F_Money ) F_Money from
(
select case when convert(varchar(8),F_DateTime,114) < '06:00:00' then convert(varchar(10),F_DateTime-1,120) else convert(varchar(10),F_DateTime,120) end F_Date , F_Money from tb
) t
group by F_Date
/*f_date F_Money
---------- -----------
2009-06-30 100
2009-07-01 500
2009-07-02 100(所影响的行数为 3 行)
*/
drop table tb
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([F_DateTime] datetime,[F_Money] int)
insert [tb]
select '2009-07-01 04:00:00',100 union all
select '2009-07-01 06:01:00',100 union all
select '2009-07-01 07:10:00',100 union all
select '2009-07-01 08:20:22',100 union all
select '2009-07-02 01:20:30',100 union all
select '2009-07-02 05:23:30',100 union all
select '2009-07-02 07:24:30',100
---查询---
select
convert(varchar(10),dateadd(ss,-(5*60*60+59*60+59),F_DateTime),120) as F_DateTime,
sum(F_Money) as F_Money
from tb
group by convert(varchar(10),dateadd(ss,-(5*60*60+59*60+59),F_DateTime),120)---结果---
F_DateTime F_Money
---------- -----------
2009-06-30 100
2009-07-01 500
2009-07-02 100(所影响的行数为 3 行)