select
sum(PCOUNT) as PCOUNT_SUM,
CONVERT(CHAR(10),PTIME,120) as PTIME
from
表
where
PTYPE=1 and PTIME between '2005-2-1 00:00:00 ' and '2005-2-4 00:00:00 '
group by
CONVERT(CHAR(10),PTIME,120)
order by
CONVERT(CHAR(10),PTIME,120)
sum(PCOUNT) as PCOUNT_SUM,
CONVERT(CHAR(10),PTIME,120) as PTIME
from
表
where
PTYPE=1 and PTIME between '2005-2-1 00:00:00 ' and '2005-2-4 00:00:00 '
group by
CONVERT(CHAR(10),PTIME,120)
order by
CONVERT(CHAR(10),PTIME,120)
不对啊,我是要求出各个时间点和,再找出1天之中和的最大值
先得到每天的每个时间点的总值,如下
PCOUNT_SUM PTIME
175 2005-2-1 5:30:00
225 2005-2-1 9:00:00
227 2005-2-2 10:00:00
110 2005-2-2 12:00:00
但上面不是你的最终结果,你要每天的最大值的那个记录,如下
PCOUNT_SUM PTIME
225 2005-2-1 9:00:00
227 2005-2-2 10:00:00
PCOUNT_SUM = max(a.PCOUNT_SUM)
PTIME = CONVERT(CHAR(10),a.PTIME,120)
from
(select
sum(PCOUNT) as PCOUNT_SUM,PTIME
from 表
where
PTYPE=1 and PTIME between '2005-2-1 00:00:00 ' and '2005-2-4 00:00:00 '
group by PTIME
order by PTIME) a
group by
CONVERT(CHAR(10),PTIME,120)
对,最终得到的精确到天就可以了to libin_ftsafe(子陌红尘)
服务器: 消息 170,级别 15,状态 1,行 3
第 3 行: 'PTIME' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,行 11
在关键字 'order' 附近有语法错误。而且数据量很大哦,不知道执行的时候速度会怎么样
create table 表(PID int,PTYPE int,PCOUNT int,PTIME datetime)
insert 表 select 1,1,100,'2005-2-1 5:30:00'
union all select 2,2,90 ,'2005-2-2 7:00:00'
union all select 3,3,160,'2005-2-1 9:00:00'
union all select 4,1,210,'2005-2-2 9:00:00'
union all select 5,2,60,'2005-2-2 7:00:00'
union all select 6,1,75,'2005-2-1 5:30:00'
union all select 7,3,84,'2005-2-2 3:00:00'
union all select 8,2,6,'2005-2-3 9:00:00'
union all select 9,1,6,'2005-2-3 9:00:00'
union all select 10,1,6,'2005-2-3 9:00:00'
union all select 11,1,60,'2005-2-3 12:00:00'
union all select 12,1,16,'2005-2-3 12:00:00'
union all select 13,1,15,'2005-2-2 9:00:00'
go
--测试
select PCOUNT_SUM = max(bb.PCOUNT_SUM),PTIME = CONVERT(CHAR(10),bb.PTIME ,120)
from
(select sum(nn.PCOUNT) as PCOUNT_SUM,nn.PTIME
from 表 nn
where nn.PTYPE=1 and nn.PTIME between '2005-1-1 00:00:00 ' and '2006-1-1 00:00:00 '
group by nn.PTIME) as bb group by CONVERT(CHAR(10),bb.PTIME,120)
go
select sum(PCOUNT) as PCOUNT_SUM,PTIME
from 表
where PTYPE=1 and PTIME between '2005-1-1 00:00:00 ' and '2006-1-1 00:00:00 '
group by PTIME
order by PTIME
--删除测试数据
drop table 表
--执行结果
PCOUNT_SUM PTIME
----------- ----------
175 2005-02-01
225 2005-02-02
76 2005-02-03(所影响的行数为 3 行)PCOUNT_SUM PTIME
----------- ------------------------------------------------------
175 2005-02-01 05:30:00.000
225 2005-02-02 09:00:00.000
12 2005-02-03 09:00:00.000
76 2005-02-03 12:00:00.000(所影响的行数为 4 行)
libin_ftsafe(子陌红尘)的少了个“,”,呵呵那么现在,如果不是每次都固定是间隔一天,有时候2天,有时候6小时
也就是说取最大值的时间段是动态的,那要怎么办呢?另外我对 SQL SERVER 性能不懂,一个表最多能有多少记录?
1000万记录的表照上面代码执行下来的时间大概是多少呢(PTYPE 和 PTIME 间建有索引)?
PCOUNT_SUM = max(a.PCOUNT_SUM),
PTIME = CONVERT(CHAR(10),a.PTIME,120)
from
(select
sum(PCOUNT) as PCOUNT_SUM,PTIME
from 表
where
PTYPE=1 and PTIME between '2005-2-1 00:00:00 ' and '2005-2-4 00:00:00 '
group by PTIME
order by PTIME) a
group by
CONVERT(CHAR(10),a.PTIME,120)
也就是说取最大值的时间段是动态的,那要怎么办呢?另外我对 SQL SERVER 性能不懂,一个表最多能有多少记录?
1000万记录的表照上面代码执行下来的时间大概是多少呢(PTYPE 和 PTIME 间建有索引)?